The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event: Join us in Stockholm, September 24-27, 2024.
Save €200 with code MSCUST on top of early bird pricing!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to 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.
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
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.
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
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the August 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
80 | |
75 | |
52 | |
50 |
User | Count |
---|---|
133 | |
124 | |
78 | |
64 | |
61 |