Forum Discussion
Calculated Column for Previous Week Sales
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?
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 )
3 Replies
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 )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?
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.
