Calculated Column for Previous Week Sales

[jackj]

Hi,

I am struggling to write dax for a calculated column, not a measure, that will provide me with the previous week's subtotal sales for a user id.  My data table is structured like this:

UserID	Sales	Week Ending
1	5	1/8/2022
2	2	1/8/2022
1	1	1/1/2022
3	3	1/1/2022
3	2	1/8/2022
1	2	1/1/2022
2	4	1/1/2022
4	1	1/8/2022
3	2	1/1/2022
2	1	1/1/2022
1	6	1/1/2022
2	3	1/8/2022

So, for example, in the first Row, I want to be able to show in a calculated column that UserID 1 had a total of 9 sales in the previous week (1/1/2022).  How can I accomplish this, dynamically based on the week ending listed in the table?

[AUaero · Accepted solution]

In that case you could rewrite the column to act as a binary flag.

Prev Week Sales Flag = 
VAR CurID = 'table'[UserID]
VAR CurWeek = 'table'[Week Ending]
VAR PrevWeek = CurWeek - 7

VAR LastWeekSales = 
CALCULATE(
    SUM('table'[Sales]),
    FILTER(
        ALL('table'),
        'table'[UserID] = CurID &&
        'table'[Week Ending] = PrevWeek
    )
)

RETURN
SWITCH(
    TRUE(),
    LastWeekSales > 5, 1
    0
)

[AUaero]

This will always provide the sum of sales for the prior week for each user ID. 

Prev Week Sales = 
VAR CurID = 'table'[UserID]
VAR CurWeek = 'table'[Week Ending]
VAR PrevWeek = CurWeek - 7

RETURN
CALCULATE(
    SUM('table'[Sales]),
    FILTER(
        ALL('table'),
        'table'[UserID] = CurID &&
        'table'[Week Ending] = PrevWeek
    )
)

Just curious why you can't use a measure here?

[jackj]

I'm looking to use it as a filter in another measure, at the UserID level - basically if last week's sales for that userID are >5, then don't count them.