Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Help creating a measure that incorporates a count if based on time series data

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:

 

courtneyhoffman_1-1650563995418.png

 

 

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:

courtneyhoffman_2-1650564624216.png

 

 

Any help you can provide is appreciated!

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

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!

View solution in original post

5 REPLIES 5
tamerj1
Super User
Super User

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!

tamerj1
Super User
Super User

Hi @Anonymous 

The screenshots are too smal and unreadable. Please provide larger readable ones along with a sample file. Thanks and have a good day. 

Anonymous
Not applicable

Here is a link to a file that contains the examples in the screenshots.

Recovered and Maintenance Holiday.xlsx

 

Links to the screenshots in case the following remain too small:

Maint Holiday.png

Recovered Maint Holiday.png

 

Maintenance Holiday:

 

2022-04-21_13h25_02.png

 

 

Recovered Maintenance Holiday:

2022-04-21_13h16_35.png

 

 

Hi @Anonymous 

the excel file link is not working

Anonymous
Not applicable

Likely permissions with OneDrive.

Is there an email I can use to provide access, a way to directly upload the XLS to this forum or an alternate method to share the example data?

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.