cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

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

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 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
Microsoft

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

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.

1.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
Highlighted
Frequent Visitor

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

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

Highlighted
Helper III
Helper III

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

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.

Highlighted
Microsoft
Microsoft

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

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.

1.PNG

 

Regards,
Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Helper III
Helper III

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

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

Highlighted
Microsoft
Microsoft

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

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper III
Helper III

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

@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.

Helpful resources

Announcements
May 2020 Community Highlights

May 2020 Community Highlights

It’s time for another PBI Community recap!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Galleries

Galleries

Looking for inspiration on how to present your data? Need instructional videos? Check out our Galleries!

Top Solution Authors
Top Kudoed Authors