Forum Discussion
Current week vs last week sum
Hi bo_afk ,
You can add allselected function to get current last week number and use it to find out correspond amount:
CW Sales = VAR CW = CALCULATE ( MAX ( Table[WeekNum] ), ALLSELECTED ( Table ) ) RETURN CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[WeekNum] = CW ) )Regards,
Xiaoxin Sheng
Hi bo_afk ,
You can use following measure formula if it suitable for your requirement:
Diff =
VAR currWeek =
MAX ( Table[WeekNum] )
RETURN
CALCULATE (
SUM ( Table[Sales] ),
FILTER ( ALLSELECTED ( Table ), [WeekNum] = currWeek ),
VALUES ( Table[Product] ),
VALUES ( Table[Market] )
)
- CALCULATE (
SUM ( Table[Sales] ),
FILTER ( ALLSELECTED ( Table ), [WeekNum] = currWeek - 1 ),
VALUES ( Table[Product] ),
VALUES ( Table[Market] )
)
If above not help, please share some sample data for test.
Regards,
Xiaoxin Sheng
Thanks for your response Anonymous.
I've however encountered an issue in cases where a particular product does not have any sales for the current week. I currently have a measure that just to looks at the CW sales figures as per below.
CW Sales =
var CW = max(Table[WeekNum])
RETURN
CALCULATE (sum (Table[Sales]), Table[WeekNum] = CW)
But it's returning results even for products that aren't sold in the CW. Looks like it is taking the max week for THAT product and then returning the sales associated to that week as opposed to the latest week overall in the data.
How can I fix this?
Hi bo_afk ,
You can add allselected function to get current last week number and use it to find out correspond amount:
CW Sales = VAR CW = CALCULATE ( MAX ( Table[WeekNum] ), ALLSELECTED ( Table ) ) RETURN CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALLSELECTED ( Table ), Table[WeekNum] = CW ) )Regards,
Xiaoxin Sheng