Forum Discussion
Anonymous
4 years agoNot applicable
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 spe...
- 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/g9Ao4uQlbQhWyjVDThe 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 ResultMaintenance 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 ResultThanks and have agreat day!
Anonymous
4 years agoNot applicable
Hello v-janeyg-msft ,
The link for the folder containig screeenshots and XLS that has dummy data and desired resuts is here:
There are permissions on the OneDrive. Is there an email I can use to grant access?
v-janeyg-msft
4 years agoCommunity Support
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