Forum Discussion

FN's avatar
FN
Regular Visitor
9 years ago

Time Intelligence Measures with dynamic Time Periods

Hello together,   I am working on a Power BI report and wasn't able to find a solution for my problem so far.   I want to calculate a so called Cancellation Rate for my company, meaning how many ...
Need Help
Phil_Seamark's avatar
Phil_Seamark
Microsoft Employee
9 years ago

Hi FN,

 

Any chance you can post a sample set of data, including a mock up of what you might expect once you have everything working?

 

Cheers,

 

Phil

FN's avatar
FN
Regular Visitor
9 years ago

Hi Phil,

 

Yes, here I have some sample data:

 

So this is my model with the realtionships:

 

Account = All the Customers with its information regarding the customer's adress, country, etc.

Contract = All Contracts with information like Contract Start Date, Contract End Date, etc (matched to Account with the Customer ID)

Subterritories = More detailled analysis for regions, so I match a subterritory (like UK) from the Customer's account to the firm of the company, Regions, etc. (not really important for the problem here)

Exchange Rate = As we have contracts in many countries, I am matching the currency from contract to Exchange rate (also not really important for this problem)

Dates = I don't know if this is necessary. It is a date table, with dates in it. As I want to measure the different things (Active Revenue, Cancelled Revenue) on a quarterly period, so I have all the dates of the start of a quarter period there, e.g. 01/01/2015, 04/01/2015, 07/01/2015 and so on. Then I am referring to this date when I am calculating how much revenue was valid at a certain date. Also the same with expired contracts. So far everything clear?

 

 

So at the moment, I am calculating the revenue that is valid like this:

=CALCULATE(SUM(Revenue); FILTER(Contracts, Contract Start Date < Dates.QuarterBeginnDate); FILTER(Contracts, Contract End Date > Dates.QuarterBeginnDate)

It is an extra column, because when I try to calculate is as a measure, the 'Dates.QuarterBeginnDate' is not possible to select in the formula from above. I don't know the reason.

Same for the expired contracts:

=CALCULATE(SUM(Revenue);FILTER(Contracts, Contract Start Date < Dates.QuarterBeginnDate); FILTER(Contract End Date > Dates.QuarterBeginnDate); FILTER(Contract End Date > Dates.Period End Date)

 

Like this, I am calculating a percentage. Also here, it is again not possible to create a measure, because these column names do not appear when I try to enter it in the formula. So I created an extra column again.

 

What I would love to have is something like this:

 

So to calculate a rate for every period. This is how I did it in Excel and then I graph it in a chart.

 

What I want in Power BI is basically the same, to calculate this (only for help) and then graph the rates in a chart. What I want is that with Slicer in Power BI I can easily re calculate the rates for different contract types, for different countries, for different Sales Partner. I don't know in this case if Power BI with its time intelligence is intelligent enough to aleady calculate the rates for periods itself or if I need these date tables or maybe something different.

Do you have any idea of how I could do that?


Thank you and Regards,
Florian