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
    Super 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)