Forum Discussion
Latest N Week(s) - Dynamic Selection
I deal with data sets that are delivered weekly which is the lowest granularity. Each file is delieverd on a Sunday. I am tring to find the best way to allow my stakeholder to select a given aggregation of weeks. In the example file there are 4 unique weeks of data.
I have successfully completed this by using a disconnected table but I can't help but feel there is a more effectice/efficent way of doin this.
Sales:=SUM( [Dollar Sales])
Selected Sales:=CALCULATE( [Sales] , FILTER( 'time' , 'time'[Index] <= [Latest N Week(s)] ) )
Latest N Week(s):=MAX([Periods])
Relationships - data[time] to time[time]
1 Reply
Anonymous , You sheet was not very clear to me. Refer last 4 week using date from the fact table.
Last 4 weeks =
var _max1 = maxx(allselected('order'), 'order'[date])
var _max = maxx(filter(all(date), date[date] = _max1), week[Rank])
return
CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=_max-4 && 'Date'[Week Rank]<=_max))You need to use a date table. Also refer by WOW blogs
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Based on today
This Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1
var _end =today()+ 7-1*WEEKDAY(today(),2)
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter
Last Week today =
var _st = today() +-1*WEEKDAY(today(),2)+1 -7
var _end =today()+ 7-1*WEEKDAY(today(),2) -7
return
CALCULATE(SUM(Sales[Sales Amount]),filter('Date','Date'[Date]>= _st && 'Date'[Date]<=_end )) //use all('Date') if need in filter
