Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
meisteryoda
Frequent Visitor

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!

 

 

 

2 ACCEPTED SOLUTIONS
lbendlin
Super User
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.

View solution in original post

Anonymous
Not applicable

// 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
    __output

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

View solution in original post

8 REPLIES 8
Anonymous
Not applicable

// 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
    __output

Bear 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 a lot! This is pretty easy and works fine! How could I not come up with this... anyway thanks! 

The problem with the first and last week I have to face now...

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.

My measure looks like this now:

 

VAR LastYear = YEAR ( LASTDATE ( Date[Date] ) ) - 1 
VAR
WeekNumber = WEEKNUM ( LASTDATE ( Date[Date] ), 2 )
VAR
DayNumberOfWeek = WEEKDAY ( LASTDATE ( Date[Date] ), 2 )
RETURN

CALCULATE (
[Sales],
FILTER ( ALL ( 'Date' ),
Date[Week] = WeekNumber &&
Date[Year] = LastYear &&
Date[Weekday] <= DayNumberOfWeek
) )

 and it seems that it works even for the first week of the year. The last week of the year I am not able to verify right now because I just have data from 2019 and 2020 and 2020 is not complete yet.

 

Why do you think that it does not work for incomplete weeks? Or in which case might it not work and why?

 

Thanks and Cheers

Anonymous
Not applicable

Very simply: If you take the first week in the second year and want to obtain the figure for the first week of the first year for comparison, you may not have enough days in the first week of the first year. So, you're not comparing like for like. If the first week in the first year has 2 days, then you're comparing 2 days against 7 days of the week in the second year. Same thing can happen (in reverse) for the last week of the last year.

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.

what part of "you will lose your sanity"  was unclear?

lbendlin
Super User
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.

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.