Forum Discussion
WTD for same week last year
Hello everyone, I am currently struggling with this for hours and the other posts in the community did not really help.
I have to calculate the WTD running Total for last years week (Monday-Sunday) with same week number as this years week.
My measure for this years WTD works fine and looks like this:
Sales WTD :=
VAR CurrentDate = LASTDATE ( Datum[Datum] )
VAR DayNumberOfWeek = WEEKDAY ( LASTDATE ( Datum[Datum] ), 3 )
RETURN CALCULATE ( [Sales], DATESBETWEEN ( Datum[Datum], DATEADD ( CurrentDate, -1 * DayNumberOfWeek, DAY ), CurrentDate ) )
But the one for same week last year makes me a headache, this is what I currently have:
Sales WTD LY :=
VAR CurrentDateLY = CALCULATE( MAX ( Datum[Datum] ), FILTER ( ALL ( Datum ), Datum[DayWeekYear] = MAX ( Datum[DayWeekYear] ) - 1 ) )
VAR DayNumberOfWeek = WEEKDAY ( LASTDATE ( Datum[Datum] ), 3 )
RETURN CALCULATE ( [Sales], DATESBETWEEN ( Datum[Datum], DATEADD ( CurrentDateLY, -1 * DayNumberOfWeek, DAY ), CurrentDateLY ) )
with Datum[DayWeekYear] = Weekday * 1000000 + WeekOfYear * 10000 + Year
somehow I have to store the date of last years day in the VAR CurrentDateLY. This date should represent the same weekday in same week as this year. E.g. for Monday in calendarweek 2 of 2020 I have to get the date of Monday in calendarweek 2 of 2019.
Any ideas how I can achieve that or maybe even with a different approach?
thanks a lot in advance!
None of your formulas use the week number. It will be much simpler if you took the week number, and then just sum up all the days in that week until the current day of the week - for both years.
// To do this properly, you have to // create some columns in your Dates table: // YearOfWeek - an int, the year the week (to which // the day belongs to) // YearWeekID - an int, the id (consecutive from 1..52) // of the week in the year // DayNumberInWeek - an int, the day (1,2,...,7) in // the week // Once you have these, you can write: [Measure WTD LY] = var __currentYear = SELECTEDVALUE( Dates[YearOfWeek] ) var __currentWeek = SELECTEDVALUE( Dates[YearWeekID] ) var __lastDayNumberInWeek = MAX( Dates[DayNumberInWeek] ) var __lastYear = __currentYear - 1 var __prevWeek = __currentWeek var __output = CALCULATE( [Sales], Dates[YearOfWeek] = __lastYear, Dates[YearWeekID] = __prevWeek, Dates[DayNumberInWeek] <= __lastDayNumberInWeek, ALL( Dates ) ) return __outputBear in mind that you'll have to give a special treatment to the first week in the second year or/and the last week in the last year since the first week in the first year in your Dates table might not have all days and the last week in your last year may not have all days. These are the edge cases that you have to tackle reasonably.
8 Replies
- lbendlin
Super User
None of your formulas use the week number. It will be much simpler if you took the week number, and then just sum up all the days in that week until the current day of the week - for both years.
// To do this properly, you have to // create some columns in your Dates table: // YearOfWeek - an int, the year the week (to which // the day belongs to) // YearWeekID - an int, the id (consecutive from 1..52) // of the week in the year // DayNumberInWeek - an int, the day (1,2,...,7) in // the week // Once you have these, you can write: [Measure WTD LY] = var __currentYear = SELECTEDVALUE( Dates[YearOfWeek] ) var __currentWeek = SELECTEDVALUE( Dates[YearWeekID] ) var __lastDayNumberInWeek = MAX( Dates[DayNumberInWeek] ) var __lastYear = __currentYear - 1 var __prevWeek = __currentWeek var __output = CALCULATE( [Sales], Dates[YearOfWeek] = __lastYear, Dates[YearWeekID] = __prevWeek, Dates[DayNumberInWeek] <= __lastDayNumberInWeek, ALL( Dates ) ) return __outputBear in mind that you'll have to give a special treatment to the first week in the second year or/and the last week in the last year since the first week in the first year in your Dates table might not have all days and the last week in your last year may not have all days. These are the edge cases that you have to tackle reasonably.
- lbendlin
Either add a disclaimer to your report that the results for incomplete weeks are not reliable, or exclude them altogether.
Do not try to come up with a solution, you will lose your sanity. Speaking out of experience.