Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to 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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.