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