Hi Everyone, Need help with WTD Last year calculations

[Karthikeyanbas]

Hi Guys, this is my data set and i am trying to calculate WTD for Last year. Both the measures i have mentioned below reutrns total value for the whole week while it should return only for the first 5 days of the week for last year. Can someone help me with this, not sure what i am doing wrong.

Dax Measure -1

LYWTD =

VAR PresentDay = MAX('Calendar'[Date])

VAR WkRnk = MAX('Calendar'[Week Rank])

VAR LastyearWR = WR - 52

VAR LYWTD_EndDate = PresentDay - 364

VAR LYWTD_StartDate = MINX(

        FILTER(

            ALL('Calendar'),

            'Calendar'[Week Rank] = WkRnk

        ),

        'Calendar'[Weekday]

    )

    RETURN

CALCULATE([Online Sales],FILTER('Calendar',('Calendar'[Date] >= LYWTD_StartDate && 'Calendar'[Date] <= LYWTD_EndDate))

Dax Measure 2

LYWTD Year = CALCULATE('Online Sales'[Online Sales],filter(ALL('Calendar'),'Calendar'[Year] = (MAX('Calendar'[Year]) -1) && 'Calendar'[Week_Number] = (MAX('Calendar'[Week_Number])) && 'Calendar'[Weekday] <= ('Calendar'[Weekday])))

Thanks

[Karthikeyanbas]

Thanks @TomMartens ,

I have sales data for years 2023 & 2024 with the info as in the table and i have a week slicer for week number.

I wish to do YOY for WTD, for any week based on the selected slicer value. I have weeke rank, week start date, week end date and everything needed.

For exmple in Week 29 for this year i have data only till 18th july, so for WTD LY i need to compare only for the respective days. 

I used this DAX Measure

LYWTD = CALCULATE('Online Sales'[Online Sales],filter(ALL('Calendar'),'Calendar'[Year] = (MAX('Calendar'[Year]) -1) && 'Calendar'[Week_Number] = (MAX('Calendar'[Week_Number])) && 'Calendar'[Weekday] <= ('Calendar'[Weekday])))

This measure returns the value for the whole week from last year and is skewing my results when i compare YOY, how do i fix this.

This should work dynamically as sales data is refreshed each day for this year, 

@amitchandak can you also pls guide me through this , I have tried the dax function from your blog but I am not sure if I have made a mistake. Thanks.

Thanks.

[v-rzhou-msft · Accepted solution]

Hi @Karthikeyanbas ,

I suggest you to ceate an unrelated weeknum table to help your calculation.

My Sample:

Table is a data table with data from 2023/01/01 to 2024/07/18.

DimDate:

Dimdate =
ADDCOLUMNS (
    CALENDARAUTO (),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] ),
    "WeekStart",
        [Date] - WEEKDAY ( [Date], 1 ) + 1,
    "WeekEnd",
        [Date] + 7
            - WEEKDAY ( [Date], 1 ),
    "WeekNumber", WEEKNUM ( [Date], 1 ),
    "Weekday", WEEKDAY ( [Date], 1 )
)

WeekRank = RANKX(VALUES(Dimdate[WeekStart]),[WeekStart],,ASC)

WeekSlicer Table:

WeekSlicer =
SUMMARIZE (
    Dimdate,
    Dimdate[Year],
    Dimdate[WeekStart],
    Dimdate[WeekEnd],
    Dimdate[WeekNumber],
    "LastDay", WEEKDAY ( MAX ( 'Table'[Date] ), 1 )
)

Measure:

Online Sales =
VAR _SelectYear =
    SELECTEDVALUE ( WeekSlicer[Year] )
VAR _SelectWeekNum =
    SELECTEDVALUE ( WeekSlicer[WeekNumber] )
VAR _LASTWEEKDAY =
    SELECTEDVALUE ( WeekSlicer[LastDay] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            Dimdate,
            Dimdate[Year]
                IN { _SelectYear - 1, _SelectYear }
                    && Dimdate[WeekNumber] = _SelectWeekNum
                    && Dimdate[Weekday] <= _LASTWEEKDAY
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[TomMartens]

Hey @Karthikeyanbas ,

here I described how to create columns in your calendar table that represent the start and end date of a calendar week.

With a given year and week number you can find the start date of a calendar week in the previous year, with this date you can create the end date to meet your requirement "the first 5 days of the prvious year's week" by adding 4 to the start date.

Then you can create a measure like so:

the measure =
CALCULATE(
<the numeric expresiion>
,ALL( '<your calendar table'> )
, DATESBETWEEN( <date column of your calendartable>, weekPrevYearStartDate, weekPrevYearStartDatePlus4Days)
) 

Hopefully this helps to tackle your challenge.

Regards,

Tom