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 spe...
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!

tamerj1's avatar
tamerj1
Community 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!