Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Solved! Go to Solution.
Hi @Anonymous
try ALL()
PrevWkSales :=
CALCULATE(
SUM('Sales'[CountOf]),
FILTER(ALL('Calendar'),
'Calendar'[WeekSequence] = 'Calendar'[WeekSequence] -1
)
)
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 😞 )
@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
@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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
82 | |
70 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |