Time Intelligence Measures with dynamic Time Periods

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

Phil_Seamark

v-huizhn-msft

I don't know if you receive a notification when I didn't mark you in my replying post.

If you received a notification, then I am sorry for this post. :)

Thank you so much for your help,

Florian