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

[Anonymous]

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.

[Anonymous]

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!