cancel
Showing results 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

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.

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
Community Support

My Sample:

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

DimDate:

``````Dimdate =
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.

3 REPLIES 3
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.
Community Support

My Sample:

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

DimDate:

``````Dimdate =
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.

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>
, 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

Announcements

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

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors