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
johnt75's avatar
johnt75
Icon for Super User rankSuper 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
odas's avatar
odas
Frequent Visitor
4 years ago

Thank you, johnt75 . This works perfect!