Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Average 4wk unit sales

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!
Need Help
  • ToddChitt's avatar
    ToddChitt
    2 years ago

    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} )

     

1 Reply

  • ToddChitt's avatar
    ToddChitt
    Icon for Super User rankSuper User
    2 years ago

    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} )