Forum Discussion
Year to Week Calculation for same Week Number and Weekday Last Year
Try
YTW LY = var currentDate = MAX('Date'[Date]) var dateLastYear =IF( ISINSCOPE('Date'[Date]), LOOKUPVALUE('Date'[Date],'Date'[Day Of Week Number], SELECTEDVALUE('Date'[Day Of Week Number]), 'Date'[WEEKNUM], SELECTEDVALUE('Date'[WEEKNUM]), 'Date'[Year], YEAR(currentDate) - 1 ), CALCULATE( MAX('Date'[Date]),'Date'[WEEKNUM] = SELECTEDVALUE('Date'[WEEKNUM]) && 'Date'[Year] = YEAR(currentDate) - 1) ) var result = CALCULATE( [Value], REMOVEFILTERS('Date'), DATESBETWEEN( 'Date'[Date], DATE( YEAR(currentDate) -1, 1,1), dateLastYear) ) return result
Super Userodas , The few option you have
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
WeekDay = WEEKDAY([Date],2) //monday
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW format
measures
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)))
This week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))
This WTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))
Last year WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week]) && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))
WTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank]) && 'Date'[WeekDay]<=max('Date'[WeekDay])))
LWTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1 && 'Date'[WeekDay]<=max('Date'[WeekDay]) ))
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=pnAesWxYgJ8
Thank you for getting back to me so quickly.
I’ve tested your proposed solution, but it doesn’t fully solve my problem.
The LWTD measure you provided calculates cumulated values within one week. What I’m trying to achieve is a cumulated value from January 1st up until same week last year.
For example, since today is Mach 29th 2022 last full week is week 12. The YTW LY measure should calculate a cumulated sum from January 1st 2021 until March 28th 2021, since March 28th 2021 is Sunday (last day of week) for week 12 2021. When drilling down to date, I would like the measure to show cumulated values from January 1st up until same weekday and same week number last year.
I’ve tried to set up an example of what I’m trying to achieve. In the example data I’m assuming January 26th is the last date of current year. The preferred result is in the second picture.
Example data
Week Date Value 53 01/01/2021 4 53 02/01/2021 1 53 03/01/2021 3 1 04/01/2021 2 1 05/01/2021 4 1 06/01/2021 2 1 07/01/2021 3 1 08/01/2021 3 1 09/01/2021 9 1 10/01/2021 7 2 11/01/2021 2 2 12/01/2021 4 2 13/01/2021 3 2 14/01/2021 7 2 15/01/2021 3 2 16/01/2021 9 2 17/01/2021 10 3 18/01/2021 9 3 19/01/2021 8 3 20/01/2021 4 3 21/01/2021 7 3 22/01/2021 9 3 23/01/2021 2 3 24/01/2021 1 4 25/01/2021 4 4 26/01/2021 10 52 01/01/2022 8 52 02/01/2022 4 1 03/01/2022 10 1 04/01/2022 6 1 05/01/2022 4 1 06/01/2022 8 1 07/01/2022 5 1 08/01/2022 3 1 09/01/2022 3 2 10/01/2022 7 2 11/01/2022 6 2 12/01/2022 5 2 13/01/2022 1 2 14/01/2022 10 2 15/01/2022 8 2 16/01/2022 6 3 17/01/2022 9 3 18/01/2022 6 3 19/01/2022 10 3 20/01/2022 10 3 21/01/2022 2 3 22/01/2022 8 3 23/01/2022 10 4 24/01/2022 5 4 25/01/2022 10 4 26/01/2022 8 Preffered result
Week YTW YTW LY Week 1 266 150 03/01/2022 22 10 04/01/2022 28 14 05/01/2022 32 16 06/01/2022 40 19 07/01/2022 45 22 08/01/2022 48 31 09/01/2022 51 38 Week 2 789 534 10/01/2022 58 40 11/01/2022 64 44 12/01/2022 69 47 13/01/2022 70 54 14/01/2022 80 57 15/01/2022 88 66 16/01/2022 94 76 Week 3 1,380 1,257 17/01/2022 103 85 18/01/2022 109 93 19/01/2022 119 97 20/01/2022 129 104 21/01/2022 131 113 22/01/2022 139 115 23/01/2022 149 116 Is this possible to solve in Power BI?
