Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi All,
I’ve encountered a problem which I cannot seem to solve and would highly appreciate your help.
I’m building a self-service data set and my end users want a year to week last year (YTW LY) measure which calculates accumulated sales for last year’s corresponding week number and weekday, e.g., cumulated sales up to Monday in Week 13.
I’m able to calculate YTW current year and YTW last year and the result is correct when visualized per week number. Where I’m having trouble is when drilling down to dates. For YTW the result is correct, but for YTW last year the sales value per date is the sum of all weekdays and not the accumulated sale of all previous days. For example, for Monday January 17th 2022 (week 3 2022) YTW LY returns the sum of the three first Mondays of 2021 and not the cumulative sum of all days up until January 18th 2021 which is the result I am trying to achieve.
The picture below shows my YTW and YTW LY (last year) measures in a table. The YTW measure is correct and cumulates the values up until a given date. The YTW LY measure cumulates the values correctly at the week number level of the matrix, but only sums similar weekdays at the date level of the matrix.
My model is simple with a calendar with all dates from 2019 to 2024 with columns such as WeekNumber, Year and WeekdayNumber. The calendar table is related to my sales fact table through a one-to-many relationship on the date column. My sales fact table has sales data on a daily granularity.
My measures are as follow
Value = sum(FakSalg[KgLtrAntall])
Value YTD = CALCULATE (
CALCULATE ( SUM ( Facsale[Value] ), DATESYTD ( DimCalendar [Date] ) ),
FILTER ( DimCalendar, DimCalendar [RelativeDay] <= 0 )
)
Value YTW = CALCULATE (
[Value YTD],
FILTER (
DimCalendar,
DimCalendar [WeekNumber] >= 1
&& DimCalendar [RelativeWeek] < 0
)
)
Value YTW LY = CALCULATE (
[Value],
FILTER (
ALL ( DimCalendar),
DimCalendar [WeekNumber] <= MAX ( DimCalendar [WeekNumber] )
&& DimCalendar [WeekdayNumber] >= MIN ( DimCalendar [Weekdaynumber] )
&& DimCalendar [WeekdayNumber] <= MAX ( DimCalendar [WeekdayNumber] )
&& DimCalendar [Year] >= MIN ( DimCalendar [Year] ) - 1
&& DimCalendar [Year] <= MAX ( DimCalendar [Year] ) - 1
)
)
Do any of you have a good solution on how to achieve a YTW LY measures which accumulates the sales value up until same weekday same week last year?
Solved! Go to Solution.
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
Hi @odas ,
Please try the following formula:
YTW LY =
VAR CurrMinDate =
CALCULATE ( MIN ( DimCalendar[Date] ), ALLSELECTED ( DimCalendar[Date] ) )
VAR LYMinDate =
CALCULATE (
MIN ( DimCalendar[Date] ),
FILTER (
ALL ( DimCalendar ),
DimCalendar[WeekNumber] = MAX ( DimCalendar[WeekNumber] )
&& DimCalendar[Year]
= MAX ( DimCalendar[Year] ) - 1
)
)
VAR diff =
DATEDIFF ( CurrMinDate, LYMinDate, DAY )
RETURN
CALCULATE (
SUM ( Facsale[Value] ),
FILTER (
ALL ( DimCalendar ),
DimCalendar[Date]
<= MAX ( DimCalendar[Date] ) + diff
&& DimCalendar[Year]
= MAX ( DimCalendar[Year] ) - 1
)
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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
@odas , 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-La...
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?