cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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

3 REPLIES 3
Regular Visitor

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

Regular Visitor

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

Super User

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors