Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Complex Measure (timeseries / count)

I am doing reporting on annual reoccuring revenue (ARR).  There are instances where customers get a break in billing, for whatever reason, and therefore the ARR for the period goes to 0.  Using a specific rule, instead of counting the 0 ARR as churn/loss in ARR, we want to track these instances as Maintenance Holiday and when the ARR resumes, rather then counting the ARR as New, we want to define this as Recovered Maintenance holiday.

 

I am looking for help on how to create these 2 measures, as other measures are dependent upon whether or not a period is classified as recovered/maintenence holiday.

 

More details follow - 

 

Maintenance holiday:

Maintenance Holiday is defined as lost revenue from a customer not on maintenance from the last period to the next for a span of 12 months or shorter.

 

Example  ABC Customer for reporting Period 12/31/2019:

Customer did not have any ARR (<=0) as of 12/31/2019 but had ARR in the prior period (12/31/2018).

"Look forward" to see if the next period has ARR (12/31/2020), if yes, check each month from last period (12/31/2018) to the month prior to the next (12/31/2020), and count how many periods have <=0 ARR. 

If the count <= 12, the decrease in ARR  for 12/31/2019 is classified as Maintenance Holiday (and NOT churn).

 

**this measure is dynamic based on the reporting period (ie for reporting period 12/31/2020, we would be checking the count of months without an ARR value from 12/31/2019 to 11/31/2021. 

**Also, in the current periods, we don't always have sufficient data to do a complete "look forward", so I need a method to specify the MAX lookforward date.  For example, reporting period 1/31/2022, the MAX look-forward date used was 3/31/2022 (instead of 1/31/2023) because we do not have that data yet.

 

Excel Formula below:

 

 

Recovered maintenance holiday:

Recovered Maintenance holiday is defined as recovered revenue from a customer within the last 2 periods where the customer was not on maintenance for 12 months or shorter . This amount is classified as Recovered Maintenance holiday (and not Recovered/New).

 

Example  XYZ Customer for reporting Period 12/31/2019:

Customer has ARR in the reporting period (12/31/2019) but did not have any ARR (<=0) in the prior period (12/31/2018).

Look backwards 2 periods (to 12/31/2017) and count how many periods have >0 ARR. 

If the count >12, the ARR  for 12/31/2019 is classified as Recovered Maintenance Holiday (and NOT new).

 

**this measure is dynamic based on the reporting period (ie for reporting period 12/31/2020, we would be checking the count of months with an ARR value from 12/31/2018 to 12/31/2020). 

 

Excel Formula below:

 

 

Any help you can provide is appreciated!

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Anonymous 
    Appologies for the late response and thank you for your patience.
    Here is the sample file with the solution https://www.dropbox.com/t/g9Ao4uQlbQhWyjVD

    The measures are 

     

     

    Starting Period = SELECTEDVALUE ( 'Data set'[Month End] )
    Ending Period = 
VAR StartOfPeriod = [Starting Period]
RETURN
    DATE ( YEAR ( StartOfPeriod ) +1, MONTH ( StartOfPeriod ), DAY ( StartOfPeriod ) )
    Extended Start = 
VAR StartOfPeriod = [Starting Period]
RETURN
    DATE ( YEAR ( StartOfPeriod ) -1, MONTH ( StartOfPeriod ), DAY ( StartOfPeriod ) )
    Recovered Maintenance Holiday = 
VAR StartPeriod = [Starting Period]
VAR EndPeriod = [Ending Period]
VAR ExtendedPeriod = [Extended Start]
VAR StartARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = StartPeriod )
VAR EndARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = EndPeriod )
VAR AboveZeroARR = 
    COUNTROWS ( 
        CALCULATETABLE ( 
            'Data set', 
            ALL ('Data set'[Month End] ), 
            'Data set'[Month End] >= ExtendedPeriod,
            'Data set'[Month End] <= EndPeriod,
            'Data set'[Total_ARR] > 0 
        ) 
    )
VAR Result =
    IF ( 
        StartARR <= 0 && EndARR >= 0 && AboveZeroARR > 12,
        EndARR - StartARR,
        0
    )
RETURN 
    Result
    Maintenance Holiday = 
VAR StartPeriod = [Starting Period]
VAR EndPeriod = [Ending Period]
VAR ExtendedPeriod = [Extended Start]
VAR StartARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = StartPeriod )
VAR EndARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = EndPeriod )
VAR AboveZeroARR = 
    COUNTROWS ( 
        CALCULATETABLE ( 
            'Data set', 
            ALL ('Data set'[Month End] ), 
            'Data set'[Month End] >= ExtendedPeriod,
            'Data set'[Month End] <= EndPeriod,
            'Data set'[Total_ARR] > 0 
        ) 
    ) 
VAR Result =
    IF ( 
        StartARR >= 0 && EndARR <= 0 && AboveZeroARR > 12,
        EndARR - StartARR,
        0
    )
RETURN 
    Result

     

     

    Thanks and have agreat day!

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Anonymous 
    Appologies for the late response and thank you for your patience.
    Here is the sample file with the solution https://www.dropbox.com/t/g9Ao4uQlbQhWyjVD

    The measures are 

     

     

    Starting Period = SELECTEDVALUE ( 'Data set'[Month End] )
    Ending Period = 
VAR StartOfPeriod = [Starting Period]
RETURN
    DATE ( YEAR ( StartOfPeriod ) +1, MONTH ( StartOfPeriod ), DAY ( StartOfPeriod ) )
    Extended Start = 
VAR StartOfPeriod = [Starting Period]
RETURN
    DATE ( YEAR ( StartOfPeriod ) -1, MONTH ( StartOfPeriod ), DAY ( StartOfPeriod ) )
    Recovered Maintenance Holiday = 
VAR StartPeriod = [Starting Period]
VAR EndPeriod = [Ending Period]
VAR ExtendedPeriod = [Extended Start]
VAR StartARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = StartPeriod )
VAR EndARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = EndPeriod )
VAR AboveZeroARR = 
    COUNTROWS ( 
        CALCULATETABLE ( 
            'Data set', 
            ALL ('Data set'[Month End] ), 
            'Data set'[Month End] >= ExtendedPeriod,
            'Data set'[Month End] <= EndPeriod,
            'Data set'[Total_ARR] > 0 
        ) 
    )
VAR Result =
    IF ( 
        StartARR <= 0 && EndARR >= 0 && AboveZeroARR > 12,
        EndARR - StartARR,
        0
    )
RETURN 
    Result
    Maintenance Holiday = 
VAR StartPeriod = [Starting Period]
VAR EndPeriod = [Ending Period]
VAR ExtendedPeriod = [Extended Start]
VAR StartARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = StartPeriod )
VAR EndARR = CALCULATE ( SUM ('Data set'[Total_ARR] ),'Data set'[Month End] = EndPeriod )
VAR AboveZeroARR = 
    COUNTROWS ( 
        CALCULATETABLE ( 
            'Data set', 
            ALL ('Data set'[Month End] ), 
            'Data set'[Month End] >= ExtendedPeriod,
            'Data set'[Month End] <= EndPeriod,
            'Data set'[Total_ARR] > 0 
        ) 
    ) 
VAR Result =
    IF ( 
        StartARR >= 0 && EndARR <= 0 && AboveZeroARR > 12,
        EndARR - StartARR,
        0
    )
RETURN 
    Result

     

     

    Thanks and have agreat day!

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello v-janeyg-msft ,

       

      The link for the folder containig screeenshots and XLS  that has dummy data and desired resuts is here:

      Maintenance Holiday Folder 

       

      There are permissions on the OneDrive.  Is there an email I can use to grant access?

      • v-janeyg-msft's avatar
        v-janeyg-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi, Anonymous 

         

        Due to the nature of the work I can't provide. You can set your link to public, please be careful to hide your privacy.

         

        Best Regards,
        Community Support Team _ Janey