12m rolling do not show line if no actuals

[Anonymous]

Hi all, 

I have used this formula to produce a 12 month moving average line: 

12m Rolling =

CALCULATE(CALCULATE(

[in M] ,DATESINPERIOD(

dim_Calendar[Date] ,MAX(dim_Calendar[Date])

,-12

,MONTH

)

), Ranked_Version[Version]="Actual")/12

My issue here is that on the months which i do not have actuals e.g., Nov and Dec, I do not want to show any value in the moving average line but instead show blank. 

Thanks all for your help! 

[MartynRamsden · Accepted solution]

Hi @Anonymous 

You could use an IF function to test your 'Actual' measure and if it's blank, return blank, otherwise calculate your 12 month average.

Something like this:

12m Rolling =
VAR Actual = [Actual Measure]
VAR Result =
    IF (
        ISBLANK ( Actual ),
        BLANK (),
        CALCULATE (
            CALCULATE (
                [in M],
                DATESINPERIOD ( dim_Calendar[Date], MAX ( dim_Calendar[Date] ), -12, MONTH )
            ),
            Ranked_Version[Version] = "Actual"
        ) / 12
    )
RETURN
    Result