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
v-huizhn-msft's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

Hi FN,

Based on my understanding, you can transfer the formula to DAX, which is used to create a measure.

active amount=CALCULATE(SUM(Table[Revenue]),Filter(Table, AND(Table[contract start data]<DateX[Date],Table[contract end data]>DateX[Date])))


Then create a measure to calculate the amount of concelled contracts.

 

concelled amount=CALCULATE(SUM(Table[Revenue]),Filter(Table, Table[contract end data]>=DateX[Date1],Table[contract end data]<=DateX[Date2])))


Then calculate the percentage.

percentage=[active amount]/[concelled amount]


Then you can create a line chart, then select the Datex[Date] as axis, the [percentage] as value level, you will get expected result.

If this is not want you want, please post the sample data as Phil_Seamark suggested.

Best Regards,
Angelia

  • FN's avatar
    FN
    Regular Visitor
    9 years ago

    Hi Angelia,

     

    I posted some sample data minutes ago.
    I already tried it like in your suggestion, but as the coloumns Dates['Period Dates'] and Contracts['Contract Start Date'] are in different tables, I can't refere to both in one measure unfortunately, but I can do so in an extra column. I don't know why this is the case or what I am doing wrong here.

     

    This is the column I have:

     

    Do you know why this doesn't work as a measure?

    In the analysis part I then want to split this rate up into different contract types, regions, etc. As long as the rate is just an extra column it is not working unfortunately.

     

    Thank you,

    Florian