Filter to last 4 week ending dates

[Anonymous]

I am trying to create a measure that calculates the total sales in the last 4 weeks (rolling), this is normally a simple job. But i seem to be having issues as am dealing with week ending dates only and the latest date is always a couple weeks old. At the moment i am trying the below DAX. I have also attached some sample data as well.

19/09/2020 00:00	9897842
10/10/2020 00:00	242
17/10/2020 00:00	245245245
24/10/2020 00:00	24525
23/05/2020 00:00	56632
13/06/2020 00:00	13563
04/07/2020 00:00	3535321
15/08/2020 00:00	1242
05/09/2020 00:00	123468464

Sales (last n months) =
CALCULATE (
    SUM ( Sales[Sales] ),
    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Date'[Date] ), – [N Value], MONTH )
)

[v-janeyg-msft · Accepted solution]

Hi, @Anonymous 

It’s my pleasure to answer for you.

According to your description,I think you can modify your measure.

Like this:

Sales (last n months) =
CALCULATE (
    SUM ( Sales[Sales] ),
    DATESINPERIOD ( ‘Date'[Date], MAX ( ‘Sales'[Date] ), – [N Value], MONTH )
)

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , You can create date/week table and week Rank and then use it.

example

new columns

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)

measure

Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))