Forum Discussion

odas's avatar
odas
Frequent Visitor
4 years ago
Solved

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?

Time Intelligence
Year to week
YTW
  • johnt75's avatar
    johnt75
    4 years ago

    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

5 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    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
  • v-kkf-msft's avatar
    v-kkf-msft
    Community Support
    4 years ago

    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.

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    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-Last-Week/ba-p/1051123
    https://www.youtube.com/watch?v=pnAesWxYgJ8

    • odas's avatar
      odas
      Frequent Visitor
      4 years ago

      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 

      WeekDateValue
      5301/01/20214
      5302/01/20211
      5303/01/20213
      104/01/20212
      105/01/20214
      106/01/20212
      107/01/20213
      108/01/20213
      109/01/20219
      110/01/20217
      211/01/20212
      212/01/20214
      213/01/20213
      214/01/20217
      215/01/20213
      216/01/20219
      217/01/202110
      318/01/20219
      319/01/20218
      320/01/20214
      321/01/20217
      322/01/20219
      323/01/20212
      324/01/20211
      425/01/20214
      426/01/202110
      5201/01/20228
      5202/01/20224
      103/01/202210
      104/01/20226
      105/01/20224
      106/01/20228
      107/01/20225
      108/01/20223
      109/01/20223
      210/01/20227
      211/01/20226
      212/01/20225
      213/01/20221
      214/01/202210
      215/01/20228
      216/01/20226
      317/01/20229
      318/01/20226
      319/01/202210
      320/01/202210
      321/01/20222
      322/01/20228
      323/01/202210
      424/01/20225
      425/01/202210
      426/01/20228

       

      Preffered result

      WeekYTWYTW 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?