Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 w...
Need Help
  • az38's avatar
    az38
    6 years ago

    Hi Anonymous 

    try ALL()

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

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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's avatar
    Anonymous
    Not applicable
    6 years ago

    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)