Year to Week Calculation for same Week Number and Weekday Last Year

[odas]

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?

[v-kkf-msft]

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.

[johnt75 · Accepted 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

[odas]

Thank you, @johnt75 . This works perfect!

[amitchandak]

@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

[odas]

@amitchandak

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?