Moving Sales average for 3 Months

DAX DAX Calculations
1

HI ,

I’m not getting the correct answer with this formula below .What I am trying to get is just 3 months MA Average .

Sales 3M MA =
AVERAGEX(
DATESINPERIOD(Dates[Date],LASTDATE(Dates[Date]),-3,MONTH),
[Total Sales])

So , What I did was create measure below

Sales Last 2M = CALCULATE([Total Sales],DATEADD(Dates[Date],-1,MONTH))
Sales Last 3M = CALCULATE([Total Sales],DATEADD(Dates[Date],-2,MONTH))
Total Sales = SUM(Sales[Line Total])

Then I added this 3 .
Roling 3 month sales = [Total Sales]+[Sales Last 2M]+[Sales Last 3M]

Then Divided by 3 ,
Sales 3M MA AK = [Roling 3 month sales]/3

Is there a better way to do this , so I if Im doing for 6 months Moving Average - I dont need to create 6 measure and add them then divide 6 .

AK Enterprise DNA Certificate - Module 2 .pbix (417.1 KB)

2

Hi,
The formula works fine, it is the context you need to see. This MA set off against DAY will show the moving average sales per day over the months. Change your formula as below, DAY instead of Month and place in the table, you will see the correct MA.

PL Sales 3Days MA = AVERAGEX (DATESINPERIOD( Dates[Date]; MAX(Dates[Date] ); -3; DAY ); [Total Sales] )

Paul

1 Like
3

One thing to keep in mind is what happens if there is a date that has no sales. If you use DATESINPERIOD it will count that as zero and use that in the average, which would skew it. There’s ways to account for that, but if there are sales on everyday then using the builtin time intelligence functions work great.

Also be sure to mark your data table as one:

How%20to%20Mark%20as%20Date%20Table
How to Mark as Date Table.png1180×650 34.4 KB

1 Like
4

HI Nick , Thank you . I did try the inbuilt Rolling Average Function , i did set my date table as date table , however got this "Only PBI - supported date hierarchies are supported .

5

Thanks Paul Let me try and get back to you .

6

I have never seen this before. Can you upload the pbix file that is producing this error?

7

Odd, never seen before, It did not happen when I checked whether it was and it was marked as date table. Made a slight change to the date table, adding a column with month&year starting on 1-1-2012 . I do recall that at the time of certification some issues with the date query script forced me to create my own.

8

Hi Paul , I tried this i am still not getting the correct answer ?
Is it because im arranging it by Month-Year ?

The correct answer is the Sales 3M MA AK - with I did the long way .
at this point if the sales is zero ( its ok to add to the Moving average

image
image.png641×625 48.2 KB

I manage to get the table to be mark as date table , the Only PBI - supported date hierarchies are supported is pop up when i use the new measure to create rolling average

image
image.png692×741 22.7 KB

Thanks you

9

You’re getting this error because quick measures required the in-built heirarchy from a date table.

This is explained here.

I just don’t recommend using quick measures. Learning DAX is the key. Everything you could possibly want to do is easy enough to create just by writing out DAX.

Thanks
Sam