Forum Discussion

Quiny_Harl's avatar
Quiny_Harl
Icon for Advocate III rankAdvocate III
5 years ago
Solved

3 month rolling average (formula not working)

Hello,   I need a simple 3-month rolling average calculation for total spent, but for some reason my formula is not working, neither any other formula I have tried so far. My moving average gives t...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    Quiny_Harl , Hope you have taken year month from date table in visual and date table is marked as a date table

     

    3M Roll. Avg (Spent) =

    CALCULATE(
    AVERAGEX( VALUES ('DATE'[Year Month]), [Total Spent] ),
    DATESINPERIOD('DATE'[Date],MAX('DATE'[Date]),-3,MONTH)
    )

     

     

    Or try like this example on my meaures

     

    Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
    CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

Quiny_Harl , Hope you have taken year month from date table in visual and date table is marked as a date table

 

3M Roll. Avg (Spent) =

CALCULATE(
AVERAGEX( VALUES ('DATE'[Year Month]), [Total Spent] ),
DATESINPERIOD('DATE'[Date],MAX('DATE'[Date]),-3,MONTH)
)

 

 

Or try like this example on my meaures

 

Rolling 3 = divide( CALCULATE(sum(Sales[Sales]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date]),-3,MONTH)) ,
CALCULATE(distinctCOUNT('Date'[Month Year]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-3,MONTH), filter(Sales,not(isblank(sum(Sales[Sales]))))))

  • Quiny_Harl's avatar
    Quiny_Harl
    Icon for Advocate III rankAdvocate III
    5 years ago

    amitchandak  Thank you so much - the reason behind my issue was because my date table was not marked as a date table. For anyone else wondering, you need to do this: 

    https://www.youtube.com/watch?v=i8aKjGZd5kY&t=4m53s

    Edit: I also figured out that if you don't have your date table marked as a date table you can still make the measure work by including the ALL(Date) statement as a second filter in the CALCULATE statement. If the Date table is marked as a date table, then the ALL statement is not needed because it is automatically added by the engine. So in my case it will look like this:

    3M Roll. Avg (Spent) =
    var LastSelectedDate = MAX('DATE'[Date])
    var Period = DATESINPERIOD('DATE'[Date],LastSelectedDate,-3,MONTH)
    var Result =
    CALCULATE(
    AVERAGEX( VALUES ('DATE'[Year Month]), [Total Spent] ),
    Period,
    ALL('DATE')
         )
    return Result