Getting monthly average instead of moving 12 month average in a line chart

[siddhantk989]

Hi,

  I am trying to create a measure for last 12 month rolling average using the following formula:

  12M Moving Avg = CALCULATE(AVERAGE(MasterData[Actual Sales]),DATESINPERIOD(MasterData[GL Date],LASTDATE(MasterData[GL Date]),-12,MONTH))

  The thing is that when I am using this measure in line chart instead of calculating rolling 12 month average it is calculating average of the current month. I am not able to understand the cause.

  Also if I am trying to create a calculated column instead of a measure I am getting an error that i do not have enough memory to perform this operation.

  Any suggestions on this one?

Thanks,

Siddhant 

[v-yulgu-msft · Accepted solution]

Hi @siddhantk989,

In source table, create two calculated columns as below:

12 month ago =
DATEADD ( MasterData[GL Date].[Date], -12, MONTH )

rolling average =
DIVIDE (
    CALCULATE (
        SUM ( MasterData[Actual Sales] ),
        FILTER (
            ALL ( MasterData ),
            MasterData[GL Date] > EARLIER ( MasterData[12 month ago] )
                && MasterData[GL Date] <= EARLIER ( MasterData[GL Date] )
        )
    ),
    12
)

Result.

Regards,
Yuliana Gu

[siddhantk989]

Hi @v-yulgu-msft,

  Thanks for the solution. Also can you pelase help me how to use this calculation where I want to calculate it for just past 12 months.

  I mean I want to show the 12M moving average for just past 12 months

[v-yulgu-msft]

Hi @siddhantk989,

The DAX provided in my original post is calculating the moving average for past 12 months. So, I am confused about your question, would you please elaborate it with some examples?

Regards,
Yuliana Gu

[siddhantk989]

@v-yulgu-msft

It is providing rolling avergae for past 12 months. But what i want to show is moving 12 month average for just past 12 moths, i.e,  what was my moving 12 m average in June 2016 - May 2017. I have data from 2012 to last day.

[JordanAWARE]

you might be able to get away with something like this:

12m moving average = 

CALCULATE (
average('MasterData'[ActualSales]),
'MasterData'[GL Date] > TODAY()-365)

this will give average sales of the last year from the day you are viewing the report

In terms of working with that line chart try replace with this:

12m moving average = 

CALCULATE (
average('MasterData'[ActualSales]),
'MasterData'[GL Date] > MAX(MasterData'[GL Date]-365))

this will hopefully give you the average for the previos year at each date interval on your graph

[siddhantk989]

Hi @JordanAWARE,

 Thanks for replying but the solution is not working. I am getting an error saying MAX function can not be used in True/Fasle condition.