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
Pulyk
Frequent Visitor

4 Weeks over 4 Weeks

Hello everyone. Need some help with the last piece of a Measure.
The data I work with is aggregated in 4 weeks period and I need to calculate 4 Weeks over 4 Weeks. So, I created a Measure for Previous Week but the issue in here is once I'm on first 4 weeks of a year, for Previous Week I want to see values from the last 4 weeks from a last Year.
For Example, for Retail Sales Prior 4 Week for 1/29/2022 I need to see $36,097,615.45; For 1/28/2023 - $36,586,514,45. You can see there are blank values on those spots now.
It is a flat table, no separate Date table.
Thanks a lot for any help.

Screenshot 2023-02-21 at 4.30.48 PM.png

2 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@Pulyk , You can use week Rank to get that

 

a new column in your date table

 

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

 

then measure like

 

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

 

 

Window function can also help

 

Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

View solution in original post

Ahmedx
Super User
Super User
4 REPLIES 4
Ahmedx
Super User
Super User

Pulyk
Frequent Visitor

Thanks a lot @Ahmedx!
Works perfectly fine.

amitchandak
Super User
Super User

@Pulyk , You can use week Rank to get that

 

a new column in your date table

 

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

 

then measure like

 

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

 

 

Window function can also help

 

Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc

Thank you @amitchandak.
Your solution scenario works for 100% accurate as well.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.