Moving 28 day average in Matrix

DAX DAX Calculations
1

Hi, is anyone able to give me some help on calculating a 28 day moving average in this matrix?

I have tried the below but I simply get the Total Sales amount, no date filters working.

28 Day Avg = AVERAGEX(DATESBETWEEN(‘Sales Day of the Week’[Date], MAX(‘Sales Day of the Week’[Date]) - 39, MAX(‘Sales Day of the Week’[Date])), calculate(SUM(‘Sales Day of the Week’[Total Sales])))

image
image937×778 49.7 KB

2

Hello @Ndz1985,

Thank You for posting your query onto the Forum.

The reason why you’re not getting the desired results is because you’re referring the “Dates” field from the Sales table and in the Matrix using the “Dates” field from the Dates table. Rather change the referencing in your measure to “Dates” field of the dates table and most importantly mark your “Dates” field as a Date table. Below is the measure somewhat should look like -

Moving Average = 
AVERAGEX(
    DATESBETWEEN(  
        Dates[Date] , 
        MAX( Dates[Date] ) - 39 , 
        MAX( Dates[Date] ) ) , 
    SUM( 'Sales Day of the Week'[Total Sales] ) )

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note:

1). It’s always a bit difficult for the forum members to provide the solution in a better and efficient manner without looking at the working or demo PBIX file and consumes much of their time. So if this doesn’t solve your query then please provide either working PBIX file or demo PBIX file alongwith the results that you’re expecting so that furthermore assistance can be provided accordingly.

2). In the post, you’ve mentioned that you’re loooking for 28 days moving averages but then you have considered 39 days for subtraction in your measure. Anyways, change that to 28 and you’ll get the results accordingly.

Thanks and Warm Regards,
Harsh

Moving Average - Harsh.pbix (656.3 KB)

2 Likes
3

Hi,

The table I’m using is a calculated table that won’t allow me to create a relationship with the dates table because it is a circular dependency.

4

Hello @Ndz1985,

As suggested above, it’s difficult to provide solution without looking at the file. It consumes time for the forum members to provide solution without having a look at the file and just involves guessing work and increases the length of the thread.

Please either provide the working or demo PBIX file for the reference so that solution can be provided accordingly.

Thanks and Warm Regards,
Harsh

5

Hi @Ndz1985, we’ve noticed that no response has been received from you since November 17.

We waited for the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Due to inactivity, we’ll be tagging this post as Solved.