Previous Week using a Week Sequence

[Anonymous]

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)

[az38 · Accepted solution]

Hi @Anonymous 

try ALL()

PrevWkSales :=
CALCULATE(
SUM('Sales'[CountOf]),
FILTER(ALL('Calendar'),
'Calendar'[WeekSequence] = 'Calendar'[WeekSequence] -1
)
)

[Anonymous]

Thanks @az38 , just gave that a whirl and doesn't return anything (as experienced in previously 😞 ) 

[amitchandak]

@Anonymous , Refer my Blog on Week for that

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123

[Anonymous]

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?

[amitchandak]

@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

[Anonymous]

@az38 & @amitchandak , I must apologise. @az38 you solution did work. My sequencing was crossing over in the weeks and that is why is wasn't working.

Thanks for your help (Y)