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

Past 12 Months sales vs rolling average.

Hi,

 

 

   I have sales data in which I have sales from year 2012 to current date. What I want to plot on a line graph is past 12 month sales vs past 12 month moving average sales. 

 

  I was able to calculate past 12 month sales but got stuck at past 12 month moving average.

 

  By 12 month moving average I mean that for current month (May 201) I should get the average for past 12 months ,i .e., May (2016) to April 2016. Similarly for April 2017 it should be average of sales between April 2016 to march 2017 and so on.

 

  Please help.

 

Thanks,

Siddhant

 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Employee
Employee

@siddhantk989

 

To calculate the moving 12 months average, you just need to have the moving 12 months total divide existing months in past 12 months. You can use DATESINPERIOD() to get the past 12 months context.

 

I assume you already have a YearMonth column in your table. You can try following formula:

 

Moving Average =
CALCULATE (
    SUM ( 'Table'[Amount] ) / DISTINCTCOUNT ( 'Table'[YearMonth] ),
    DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH )
)

44.PNG

 

 

Please also refer to blogs below:

Rolling 12 Months Average in DAX

Moving Averages, Sums, Etc.

 

Regards,

View solution in original post

1 REPLY 1
v-sihou-msft
Employee
Employee

@siddhantk989

 

To calculate the moving 12 months average, you just need to have the moving 12 months total divide existing months in past 12 months. You can use DATESINPERIOD() to get the past 12 months context.

 

I assume you already have a YearMonth column in your table. You can try following formula:

 

Moving Average =
CALCULATE (
    SUM ( 'Table'[Amount] ) / DISTINCTCOUNT ( 'Table'[YearMonth] ),
    DATESINPERIOD ( 'Table'[Date], LASTDATE ( 'Table'[Date] ), -12, MONTH )
)

44.PNG

 

 

Please also refer to blogs below:

Rolling 12 Months Average in DAX

Moving Averages, Sums, Etc.

 

Regards,

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.