Leave blank 3 month rolling average if not all 3 months have data

[dapperscavenger]

Hello! I have a rolling average formula as follows:

3mo. rolling average = CALCULATE( SUMX( VALUES( Dates[Calendar Month] ), [SS]) , DATESINPERIOD( Dates[Calendar Month], MAX( Dates[Calendar Month] ), +2, MONTH ) ) /3

I want to put it into a chart and compare it to the actuals.

For the first two months and last two months, however, the numbers go all squiffy because I don't have the previous 2 months / next 2 months data.  So on my chart I have some ugly lines at start and at end (cirlced in red here):

I'd thought to create some sort of formula along the lines of an if blank, but not sure how to do it.  

Any thoughts?

[amitchandak]

@dapperscavenger , not very clear try

Rolling 2 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-2,MONTH))

Rolling 2 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-2,MONTH))

[dedelman_clng · Accepted solution]

Hi @dapperscavenger  - something like this might work

3m RA = var __myDate = MAX(Dates[Calendar Month])
var __MaxDt = CALCULATE(MAX(Dates[Calendar Month]), ALL(Dates)) 
var __MinDt = CALCULATE(MIN(Dates[Calendar Month]), ALL(Dates)) 
RETURN 
IF ( OR(
      ABS(DATEDIFF (__myDate, __MaxDt, MONTH)) <= 2, 
        ABS(DATEDIFF (__myDate, __MinDt, MONTH)) <= 2
       ), 
       BLANK(), 
       [Your Calculation]
)

Hope this helps

David

[dapperscavenger]

Hi David,

This worked great, thank you!

I adjusted the formula ever so slightly so that it was <=1 instead of <=2 .  Then it was exactly what I needed 🙂

3mo RA = var __myDate = MAX(Dates[Calendar Month]])
var __MaxDt = CALCULATE(MAX(Dates[Calendar Month]), ALL(Dates)) 
var __MinDt = CALCULATE(MIN(Dates[Calendar Month]), ALL(Dates)) 
RETURN 
IF ( OR(
      ABS(DATEDIFF (__myDate, __MaxDt, MONTH)) <= 1, 
        ABS(DATEDIFF (__myDate, __MinDt, MONTH)) <= 1
       ), 
       BLANK(), 
       [my calculation]
)