Forum Discussion
Solved
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: ...
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.