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 share some sample data in text form rather than as an image to save lots of key-ing in :)

 

 

FN's avatar
FN
Regular Visitor
9 years ago

Hi Phil_Seamark,

 

here is some sample data for you. Thank you so much already. I really appreciate it.

 

CustomerContract TypeTotal PriceContract Start DateContract End DateAddress 1: Country (Customer)
Customer 1Update Contract1.687,448.1.20057.31.2017Austria
Customer 2Update Contract2.595,006.1.20035.31.2017United States
Customer 3Support Contract6.923,043.1.20169.30.2020Germany
Customer 4Update Contract1.119,9611.1.200610.31.2017Germany
Customer 5Support Contract4.560,007.30.20108.31.2014United States
Customer 6Cloud Agreement1.932,0010.1.20159.30.2016Spain
Customer 7Support Contract7.856,284.1.20153.31.2018United Kingdom
Customer 8Update Contract798,721.1.200112.31.2005Switzerland
Customer 9Cloud Agreement1.890,0012.1.201611.30.2017United States
Customer 10Update Contract309,001.1.201112.31.2014Isle Of Man
Customer 11Cloud Agreement343,008.15.20143.31.2017Germany
Customer 12Update Contract6.319,445.1.20024.30.2017Germany
Customer 13Update Contract4.535,1610.1.20069.30.2017Germany
Customer 14Cloud Agreement4.116,0010.1.20159.30.2017Germany
Customer 15Support Contract6.996,007.7.20118.31.2017United States
Customer 16Support Contract193,801.1.201112.31.2017Switzerland
Customer 17Update Contract232,928.1.20117.31.2017Germany
Customer 18Support Contract329,762.1.20081.31.2018Germany
Customer 19Support Contract1.644,009.1.20078.31.2008United States
Customer 20Support Contract312,007.17.20097.31.2016United States
Customer 21Support Contract2.814,0011.26.201411.30.2016Mexico
Customer 22Support Contract744,003.16.20113.31.2012Mexico
Customer 23Update Contract1.610,645.1.20024.30.2013Germany
Customer 24Update Contract1.545,2412.1.201111.30.2017Germany
Customer 25Update Contract5.355,007.30.20108.31.2017United States
Customer 26Support Contract200,002.18.19982.18.2002United States
Customer 27Update Contract1.771,203.1.200512.31.2017Germany
Customer 28Support Contract4.902,0012.31.200810.31.2017United States
Customer 29Update Contract1.279,566.1.201512.31.2017Austria
Customer 30Support Contract312,009.30.20089.30.2014United States
Customer 31Support Contract2.719,801.1.19802.28.2018United States
Customer 32Support Contract4.999,9212.10.200312.31.2017United States
Customer 33Support Contract312,006.24.20136.30.2014United States
Customer 34Support Contract32.325,009.1.20158.31.2020United States
Customer 35Cloud Agreement4.116,006.1.20165.31.2017Germany
Customer 36Support Contract8.397,0010.19.20116.30.2017Brazil
Customer 37Update Contract1.437,845.1.20154.30.2017Switzerland
Customer 38Update Contract6.624,249.1.20078.31.2017Germany
Customer 39Support Contract3.153,1211.1.201012.31.2017Germany
  • 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