Forum Discussion
Previous Week using a Week Sequence
hello,
I have 2 tables ('Sales' & 'Calendar') - calendar containing future dates - and I need to calculate the previous week based on a week sequence number (I hope this will solve the issue of wk 1 yyyy !)
I have the following (and I have tried a couple of other methods) but doesn't seem to work as expected;
PrevWkSales :=
CALCULATE(
SUM('Sales'[CountOf]),
FILTER('Calendar',
'Calendar'[WeekSequence] = 'Calendar'[WeekSequence] -1
)
)
Any help would be appreciated 🙂
Editied: probably should note the calendar is custom (not sure if makes a difference)
Hi Anonymous
try ALL()
PrevWkSales := CALCULATE( SUM('Sales'[CountOf]), FILTER(ALL('Calendar'), 'Calendar'[WeekSequence] = 'Calendar'[WeekSequence] -1 ) )
6 Replies
Anonymous , Refer my Blog on Week for that
Thanks amitchandak , I had come across this however if there are future dates in the table, the max rank doesn't work as it ranks future dates as well as the past dates?
Anonymous , In case you have a future date you need, have a filter on the page or you can modify the formula to have only have latest week
This Week = var var _max1 =maxx('order',[Order date]) var _week =maxx(filter('Date','Date'[Date]=_max1),[Week Rank]) return CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=_week)) Last Week = var var _max1 =maxx('order',[Order date]) var _week =maxx(filter('Date','Date'[Date]=_max1),[Week Rank])-1 return CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=_week))You can use Today in place of max date from table
Hi Anonymous
try ALL()
PrevWkSales := CALCULATE( SUM('Sales'[CountOf]), FILTER(ALL('Calendar'), 'Calendar'[WeekSequence] = 'Calendar'[WeekSequence] -1 ) )Thanks az38 , just gave that a whirl and doesn't return anything (as experienced in previously 😞 )
