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