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

 

There are a few moving parts to this one so rather than type it up, here is a PBIX file for you to have a play with

 

https://wgtnpowerbi-my.sharepoint.com/personal/phil_wgtnpowerbi_onmicrosoft_com/_layouts/15/guestaccess.aspx?docid=061eceb2191fa47d383e20b01625ec202&authkey=AfflUY6gebqRiKooaXOor0o

 

I think it is close, but once we have applied any tweaks, we can post more detail in here for future searches.

 

Feel free to ask any questions you have :)

FN's avatar
FN
Regular Visitor
9 years ago

Hi Phil_Seamark;

 

it took me a while to try to apply this format to the whole data set from our company.

Your solution looks really good, it is definitely going in the right direction. Thank you so much for your help already!

I only do not understand, how you created this "Processed Data" Table.
I can see that you typed in the following formula there, but I don't know where to type this formula in, when I create a new Power BI sheet with the whole data from the company. Did you do "Merge Queries" or did you start the new table with "Enter new Data"? Or is there a completely different way you did this? Once I know how to create this Processed Data Tab, I think it should be working.

 

Processed Data = ADDCOLUMNS(
     FILTER(
            CROSSJOIN('Raw Data';Quarters);
                    (
                    -- Started before start of Quarter and was active during the quarter
                    [Contract Start Date]<=[QuarterStart] &&
                    [Contract End Date] > [QuarterStart]
                    )
                ||
                    (
                    -- Started During the Quarter
                    [Contract Start Date]<=[QuarterEnd] &&
                    [Contract Start Date]>=[QuarterStart]  
                    )
                ) ;
               
                "Contracts Started" ; if([Contract Start Date]<[QuarterEnd] && [Contract Start Date]>=[QuarterStart];1;0);
                "Contracts Ended" ; if([Contract End Date]<[QuarterEnd] && [Contract End Date]>=[QuarterStart];1;0)
               
                )


I am really looking forward hearing from you.

Cheers,
Florian