Show last 5 weeks based on selected week

[joshua1990]

Hi community!

I have a table that shows the sales pro each product per date. THen I have a calendar table linked to that table to aggegrate the data into weekly buckets.

Now I would like to show in a pivot table the last 5 weeks sales in thos format

Selected Week	2024-01	2024-02	2024-03	2024-04	2024-05	2024-06
01	5	6	8	9	1
02		6	8	9	1	2
03			8	9	1	2

It is like a waterfall, showing the last 5 weeks sales based on the selected week.

How would you build that?

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

WINDOW function (DAX) - DAX | Microsoft Learn

Expected result measure: = 
VAR _selectedyearweek =
    MAX ( 'Axis table'[weekenddate] )
VAR _t =
    WINDOW (
        1,
        ABS,
        5,
        ABS,
        FILTER (
            ALL ( 'Calendar'[Year-Week], 'Calendar'[weekenddate] ),
            'Calendar'[weekenddate] >= _selectedyearweek
        ),
        ORDERBY ( 'Calendar'[weekenddate], ASC )
    )
RETURN
    CALCULATE ( SUM ( Sales[Sales] ), KEEPFILTERS ( _t ) )