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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Karthikeyanbas
Frequent Visitor

Hi Everyone, Need help with WTD Last year calculations

 

 

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.

Karthikeyanbas_0-1721415925993.png

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
1 ACCEPTED SOLUTION

Hi @Karthikeyanbas ,

 

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

My Sample:

vrzhoumsft_0-1721639737555.png

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.

vrzhoumsft_1-1721639869293.png

 

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.

 

View solution in original post

3 REPLIES 3
Karthikeyanbas
Frequent Visitor

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.

Hi @Karthikeyanbas ,

 

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

My Sample:

vrzhoumsft_0-1721639737555.png

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.

vrzhoumsft_1-1721639869293.png

 

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

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

August Carousel

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.