Complex Measure (timeseries / count)

[Anonymous]

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!

[tamerj1 · Accepted solution]

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!

[v-janeyg-msft]

Hi, @Anonymous 

Can you share a sample file with dummy data and your desired result? So we can help you soon.

You should check how to post effectively:

How to Get Your Question Answered Quickly - Microsoft Power BI Community

Best Regards,
Community Support Team _ Janey

[Anonymous]

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]

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