Forum Discussion
WTD for same week last year
- 5 years ago
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.
- Anonymous5 years ago
// 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.
Thanks for the reply, I think I know what you mean. I am actually interested in comparing the days of each week between 2 years. The first week of a year actually should just contain the days of that year and not some additional days from the previous year.
So if the first calendar week goes from 30.12-5.1 I just want the WTD for the days from the new year and then it works fine. So the WTD is not really a problem but the problem occurs when I am trying to do a YTD comparison between the two years (YTD with the same requirements --> calendar week and weekday match). Then it is possible that some days are not represented anywhere which means that the YTD Total at the end of the year or even already at the beginning might be incorrect due to some missing days.
- lbendlin5 years ago
Super User
what part of "you will lose your sanity" was unclear?