Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin 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.
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!
Solved! Go to 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!
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!
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.
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:
Maintenance Holiday:
Recovered Maintenance Holiday:
Hi @Anonymous
the excel file link is not working
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?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |