Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Mariar02
Regular Visitor

Last 4 weeks current & previous year in column

Hi, I have a big trouble to get last 4 weeks current year and same weeks previous year in column, after this I need a calculation of % etc. Maybe you can support, I would really appreciate!!!! I try to find a solution for calculated groups (would prefer to use it), but any other idea is very welcome! I put an image of Excel example: basically weeks are in columns like filter for f.e. sum of sales, which can be used in connection with calculated group. for 4 weeks current year I created a formula below:

L4W = CALCULATE( SELECTEDMEASURE(), FILTER(D_Date,DATEDIFf(D_Date[CalendarDate],TODAY(),WEEK)>0 && DATEDIFF(D_Date[CalendarDate],TODAY(),WEEK)<5) )

 

Mariar02_0-1708098317591.pngMariar02_1-1708098364205.png

 

3 REPLIES 3
Mariar02
Regular Visitor

Many, many thanks for a link for weeks calculations!!! I will try everything and will let you know.

Hello, many thanks for your support! we tried your suggested solution I was able to create all measures for last 4 weeks as a separat measure for each week👍, we need unfortunately week number solution for columns dynamic alsways last 4 weeks, also same 4 weeks LY etc. in Rows we will put measure like sum etc. As I put it in screenshort. I didn't find any  recommendations how to create them. Maybe you have some ideas. Many thanks in advance! KR Anjela

Mariar02_0-1709047792935.png

 

amitchandak
Super User
Super User

@Mariar02 ,

 

Have these new columns in Date Table, Week Rank is Important in Date/Week Table

Week Rank = RANKX('Date','Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX('Date','Date'[Year Week],,ASC,Dense) //YYYYWW format


These measures can help
This Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))

 

 

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-3 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

or

 

Last 4 weeks = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))

 

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
Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.