Average 4wk unit sales

[Kenta]

Hi,

I'm currently trying to modify a formula to show only past 4 weeks of sales. Currently, the formula shows average unit sales for all selected weeks in the dashboard.

This is the current formula:

Avg. Wk U Sls C2024 =

AVERAGEX(FILTER(ALLSELECTED('Master - Calendar Linear View'[Date].[Date]),

'Master - Calendar Linear View'[Date].[Date] <= MAX('Master - Calendar Linear View'[Date].[Date])),

[Total U C2024])

I would like it to always show the last 4 weeks of sales and not an average for all selected weeks.

Thanks!

[ToddChitt · Accepted solution]

First: Do you have a contiguous Date dimension attached to your fact table. Let's assume you do.

Create a calculated column in the Date table of [Weeks Back] = DATEDIFF(WEEK, 'Date'[Date], TODAY() )

NOTE: Not sure if the DAX is 100% accurate here, going from memory.

Now create your measure:

Last Four Weeks Sales = CALCULATE ( SUM ( <some column> ), 'Date'[Weeks Back] IN {1, 2, 3, 4} )