Year to Week Calculation for same Week Number and Weekday Last Year
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?
