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
Anonymous
Not applicable

Table showing running total up until last Tuesday vs this week.

Hi all!

 

I wanted to show last week vs this week for running totals.

 

I've created a custom 'Date' table that will contain all days.

 

measure_running_total_this_week =
CALCULATE (
    SUM ( data_table[net_quantity] ),
    FILTER (
        ALL ( 'Date' ),
        ISONORAFTER ( 'Date'[Date], MAX ( 'Date'[Date] )DESC )
    )
)
 
1. How do I modify the formula above to get the running total SUM up until last week?
2. How do get the running total up until last week TUESDAY?
 
I assume I have to modify the filtered table in RED but I can only provide a table as the parameter.
Or do I modify the GREEN line and filter for MAX date - 1 week?
 
My goal is to show a table with LAST WEEK vs THIS WEEK for running total.
 
 
 
1 REPLY 1
Anonymous
Not applicable

I think I've got it now.

 

To get last week's SUM (for Tuesday), I performed the following:

measure_running_total_last_week  =
VAR someDayLastWeek =
    TODAY () - 7
VAR modDate =
    MOD ( someDayLastWeek, 7 )
VAR lastTuesday = someDayLastWeek - modDate + 3
RETURN
    CALCULATE (
        SUM ( data_table[net_quantity] ),
        FILTER ( ALL ( 'Date' ), ISONORAFTER ( 'Date'[Date], lastTuesday, DESC ) )
    )

If there is a better way of doing this, please let me know. Thanks all!

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

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