I'm Using POWER BI
I have a set of data (see attached). I'm trying to work out what revenue has been lost year to year.
Calculating the current total revenue for active contracts is easy with a simple CALCULATE([Total Contracts],FILTER(Contracts,Contracts[System Status]="Active"))
To calculate my starting point ie Contracts that were previously active (now with a status of ARCHIVED) I need to calculate the Total Annualised Renewal by Customer for contracts with a start date before or equal to the year 2017 and an expiry date greater or equal to 31/12/17.
I've tried calculating the Prv Version to try and use it as a filter to sum the contracts but that doesn't seem to work
Prv Version = IF(MAX(Contracts[Version])=MIN(Contracts[Version],0,MAX(Contracts[Version]-1)
How can I do this?
I think I was making this more complicated than it needs to be.... but I still can’t get it to work. The measure relationship to calendar needs to be based on expiry date. I’m trying to create 2 measures: [contracts expiring yr1]: total Ann con val with the contracts table filtered for only contracts with an expiry date in between and including 1st Dec 2016 and 30 Nov 2017
[contracts expiring yr2] as above but with expiry date between 1st Dec 2017 and 30 Nov 2018
PROBLEM 1: i can’t get the between dates filter to work
I then want a table grouped by customer that includes the values
[contracts expiring yr 1]
[contracts expiring yr2]
But only for the customers that have a value for [contracts expiring yr1]
how do i do this - do I have to create a dynamic table of Yr1 customers?
I've got the measures
[contracts expiring yr2] = CALCULATE([Total Contracts by Expiry Date],DATESBETWEEN(Dates[Date],"1/12/2017","30/11/2018"))
But it double counts the values?
When using the DAX syntax as described in the Power Pivot course in POWER BI it doesn't work when the dates are spread across years
For POWER BI to work it appears the syntax has to be written as
[contracts expiring yr2] = CALCULATE([Total Contracts by Expiry Date],DATESBETWEEN(Dates[Date, DATE(2017,21,1),DATE(2018,11,30)
Hi Alison,
Sorry for the late reply, will look into your data and get back to you tomorrow.
Thank you
Catalin
Hi Allison,
If there is a Year field displayed in the report context, it's not the right way to calculated the data.
Check the file attached, I used a power query solution to generate rows for multi-year contracts, this way you will be able to get the values in that year column of the report.
Thanks but this doesn't give me the answer.
See my replies above - copied here for ease:
The measure relationship to calendar needs to be based on expiry date. I’m trying to create 2 measures: [contracts expiring yr1]: total Ann con val with the contracts table filtered for only contracts with an expiry date in between and including 1st Dec 2016 and 30 Nov 2017
[contracts expiring yr2] as above but with expiry date between 1st Dec 2017 and 30 Nov 2018
I've got the filter to work like so:
For POWER BI to work it appears the syntax has to be written as
[contracts expiring yr2] = CALCULATE([Total Contracts by Expiry Date],DATESBETWEEN(Dates[Date, DATE(2017,21,1),DATE(2018,11,30)
I then want a table grouped by customer that includes the values
[contracts expiring yr 1]
[contracts expiring yr2]
But only for the customers that have a value for [contracts expiring yr1]
how do i do this - do I have to create a dynamic table of Yr1 customers?
Being able to compare version numbers for a given date period would also be beneficial ie finding the max of the version number and comparing that with Max(version no)-1. If that equalled 0 then it would mean the contract didn't exist previously
Hi Alison,
In your 'Desired Result' table Customer A has 4000 (3000+1000) for 2017 and 3250 for 2018, but I can't see which rows in the Contracts table these values relate to. It doesn't matter which date column I refer in the Table, I'm not able to map the figures to the corresponding years.
Please clarify which Date column in the Contracts Table the Desired Result table is using to classify the Annualised Renewal Values.
Thanks,
Mynda
As per my previous comments it appears I may have been making this more complicated by trying to use start date and status. What do you think?
the date period is between 1/12 and 30/11 for each year. NB the Power BI DAX appears to be different to Power Pivot Dax you have in the video. I’ll use Power Pivot for now.
I’m trying to find which contracts were active at a given point in time based on start date and expiry date to calculate a year on year change by customer
cust A contract 1 version 1 starts 2/10/16 expires 1/10/2017. Value £1000
cust A contract 1 version 2 starts 2/10/17 expires 1/10/2018. Value £1500
cust A contract 1 version 3 starts 2/10/18 expires 1/10/2021 annual value £1200
cust B contract 3 version 1 starts 4/12/16 expires 3/12/17 Value £3000
cust B contract 3 version 2 starts 4/12/17 expires 3/12/2018. Value £2500
cust C contract 4 version 1 starts 2/2/18 expires 1/2/21
2016 = dates between 1/12/15 and 30/11/16
2017 = dates between 1/12/16 and 30/11/17
2018 = dates between 1/12/17 and 30/11/18
based on Expiry Date
2017 customer A = £1000
2018 customer A = £1500
2019 customer A = £0*
2020 customer A = £0*
2021customer A = £1200*
*as the latest contract was for 3 years it appears the customer didn’t have a contract for the years 2019 and 2020 which is incorrect both of those years should have £1200 to give the Yr on Yr change of -£300
Hi Alison,
Can you please share an example Excel file with your data before and then the desired result, so I can see how you get from your current data layout to what you want to see. It's very difficult to follow a long written description and maintain that picture in my head and then provide a solution.
Once I can see the before and after views of the data in an Excel file and follow the trail, I can then figure out the most efficient/best way to get there.
Thanks,
Mynda
Hi Alison,
Not sure if you noticed, but in the file I sent there is a measure that calculates the previous version value, it's displayed in a pivot table by year.
All you have to do is to use that measure in your calculations.
File attached.
Thanks
Hi Alison,
Your first goal should be to get your data into a transaction format where there is a row for every year's contact value i.e. a Tabular Format, as Catalin did for you with your first example. From there you can then summarise the data in year context in a PivotTable and calculate the change year on year using DAX measures.
It is overly complicated to skip this step and jump straight to DAX formulas and try and factor in different calculations for contracts containing an annual amount that is recurring over multiple years vs a single year contract. It's just too messy.
In the attached file (filtered in Power Query to include only those customers you provided examples for) you'll see Table1 has had the rows for customer IDN0001 expanded so there is a row for each year of their contract, which now matches the structure for the other customer's contracts. See Power Query session 7.08 where this technique is covered.
In Power Pivot I've used various explicit measures to calculate the different results you need. Because you only want to look at negative changes in contract values per customer you'll see there are additional steps required for this special requirement. You may be able to amalgamate some of these measures if you want, but I kept them separate to make it easier to follow and they aren't effecting performance.
The PivotTable in the attached file shows more columns than your example so that you can see those intermediary steps.
Some of the DAX functions in this file aren't covered in the course as they're quite advanced, but they are covered in the DAX eBook that gets sent out 31 days into your membership.
Many of your questions illustrate that you are trying to run before you can walk by tackling things before you've learnt how to use the tools. You really should finish the courses and complete the practice lessons before you try to take on advanced solutions.
Mynda
Thank you.
