cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
IH8DATA
New Member

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]

 

Example File

1 REPLY 1
amitchandak
Super User
Super User

@IH8DATA , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8

 

 

Based 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



Power BI Features || Datamarts: https://youtu.be/8tskWsJTEpg || Field Parameters : https://youtu.be/lqF3Wa1FllE?t=70
Time Intelligence Decoded : https://youtu.be/aU2aKbnHuWs&t=145s || 50+Power Query List Functions : Learn Power Query List
Did I answer your question? Mark my post as a solution! Appreciate your Kudos !! Proud to be a Super User! !!
Dashboard of My Blogs !! Connect on Linkedin !! Subscribe to my youtube Channel !!
Want To Learn Power BI | Beginners !! Advance Concepts !! Power BI For Tableau User !!

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors