Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
siddhantk989
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
v-yulgu-msft
Employee
Employee

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
v-yulgu-msft
Employee
Employee

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.

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

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.

@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
Frequent Visitor

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

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.