Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Reply
joshua1990
Post Prodigy
Post Prodigy

Show last 5 weeks based on selected week

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 Week2024-012024-022024-032024-042024-052024-06
0156891 
02 68912
03  8912

 

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

 

How would you build that?

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1708865865864.png

 

 

Jihwan_Kim_0-1708865845676.png

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

View solution in original post

1 REPLY 1
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_1-1708865865864.png

 

 

Jihwan_Kim_0-1708865845676.png

 

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.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors