Forum Discussion
Helper ICustom Calendar: YOY comparison to same weekday
Hi,
I am trying to create a date table for YOY comparisons.
This is seemingly simple, but it is complicated by two factors:
- I use a retail calendar: IE: Every month starts with a Sunday. Thus, April 2021 starts April 4 and ends May 1; May 2021 starts May 2 and ends May 29, etc. Some months have 4 weeks, some months have 5 weeks, etc. Here is 2020:
- My YOY comparisons need to be to the same weekday LY - April 12, 2021 is a Monday. April 12, 2020 was a Sunday. This is not an adequate comparison. I need to compare, in reality, Monday April 12,2021 to Monday April 13, 2020.
How do I build a date table that incorporates my retail year calendar and compares like-to-like days, truly apples-to-apples?
Thanks for your help!
You can make a DAX Date table like below starting on the first Sunday of your first year.
Date 445 =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 5 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" )
)Then you can add a DAX column to get the Day of the year with an expression like this. The Day numbers of each year will line up.
Day =
VAR vThisYear = 'Date 445'[Year]
VAR vFirstSunday =
CALCULATE (
MIN ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vPYFirstSunday =
CALCULATE (
MIN ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear - 1,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vDayThisYear =
DATEDIFF ( vFirstSunday, 'Date 445'[Date], DAY ) + 1
VAR vDayPrevYear =
DATEDIFF ( vPYFirstSunday, 'Date 445'[Date], DAY ) + 1
VAR vResult =
IF ( vDayThisYear <= 0, vDayPrevYear, vDayThisYear )
RETURN
vResultPat
Please try this DAX table expression. Just update the Sundays in the vSundays variable to bracket the date range.
Date 445 =
VAR vSundays =
{
DATE ( 2019, 12, 29 ),
DATE ( 2020, 12, 27 ),
DATE ( 2021, 12, 26 ),
DATE ( 2022, 12, 25 )
}
VAR vCalendar =
CALENDAR ( MINX ( vSundays, [Value] ), MAXX ( vSundays, [Value] ) - 1 )
VAR vAddDay =
ADDCOLUMNS (
vCalendar,
"Day",
VAR vFirstDOY =
MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] )
RETURN
DATEDIFF ( vFirstDOY, [Date], DAY ) + 1
)
VAR vAddYearWeek =
ADDCOLUMNS (
vAddDay,
"Year",
YEAR ( MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] ) ) + 1,
"Week", ROUNDUP ( [Day] / 7, 0 )
)
VAR vAddMonth =
ADDCOLUMNS (
ADDCOLUMNS (
vAddYearWeek,
"MonthNum",
SWITCH (
TRUE (),
[Week] <= 4, 1,
[Week] <= 8, 2,
[Week] <= 13, 3,
[Week] <= 17, 4,
[Week] <= 21, 5,
[Week] <= 26, 6,
[Week] <= 30, 7,
[Week] <= 34, 8,
[Week] <= 39, 9,
[Week] <= 43, 10,
[Week] <= 47, 11,
[Week] <= 52, 12
)
),
"Month", FORMAT ( DATE ( 2020, [MonthNum], 1 ), "MMMM" )
)
RETURN
vAddMonthPat
6 Replies
- mahoneypat
Microsoft Employee
You can make a DAX Date table like below starting on the first Sunday of your first year.
Date 445 =
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 5 ), DATE ( 2021, 12, 31 ) ),
"Year", YEAR ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" )
)Then you can add a DAX column to get the Day of the year with an expression like this. The Day numbers of each year will line up.
Day =
VAR vThisYear = 'Date 445'[Year]
VAR vFirstSunday =
CALCULATE (
MIN ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vPYFirstSunday =
CALCULATE (
MIN ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear - 1,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vDayThisYear =
DATEDIFF ( vFirstSunday, 'Date 445'[Date], DAY ) + 1
VAR vDayPrevYear =
DATEDIFF ( vPYFirstSunday, 'Date 445'[Date], DAY ) + 1
VAR vResult =
IF ( vDayThisYear <= 0, vDayPrevYear, vDayThisYear )
RETURN
vResultPat
APMHey mahoneypat ,
Thanks!
This worked generally really well, but I have one big issue:
My retail calendar has, for example, for January 2020: December 29,2019 - January 25, 2020 (the calendar I showed you previously was actually off, and I got sent an updated copy).
This is the correct calendar:
Going through the days, I noticed that it counts days 1-7 and then restarts the count on January 5th. January 5 should be the 8th day, not day 1 again. This throws the whole set off again. Is there a fix for this?
Also, having this built, I'm thinking now about the dashboard and slicers.
Since December 29, 2019 is actually the first day of January 2020 in my Calendar, when I create a slicer and select "January", the first day of my calendar's January won't show up.
Is the only way around this to hardcode aMonth column in (either manually typing in 'January' etc or with a IF formula for each month)? Or is there a better way?
- mahoneypat
Please try this DAX table expression. Just update the Sundays in the vSundays variable to bracket the date range.
Date 445 =
VAR vSundays =
{
DATE ( 2019, 12, 29 ),
DATE ( 2020, 12, 27 ),
DATE ( 2021, 12, 26 ),
DATE ( 2022, 12, 25 )
}
VAR vCalendar =
CALENDAR ( MINX ( vSundays, [Value] ), MAXX ( vSundays, [Value] ) - 1 )
VAR vAddDay =
ADDCOLUMNS (
vCalendar,
"Day",
VAR vFirstDOY =
MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] )
RETURN
DATEDIFF ( vFirstDOY, [Date], DAY ) + 1
)
VAR vAddYearWeek =
ADDCOLUMNS (
vAddDay,
"Year",
YEAR ( MAXX ( FILTER ( vSundays, [Value] <= [Date] ), [Value] ) ) + 1,
"Week", ROUNDUP ( [Day] / 7, 0 )
)
VAR vAddMonth =
ADDCOLUMNS (
ADDCOLUMNS (
vAddYearWeek,
"MonthNum",
SWITCH (
TRUE (),
[Week] <= 4, 1,
[Week] <= 8, 2,
[Week] <= 13, 3,
[Week] <= 17, 4,
[Week] <= 21, 5,
[Week] <= 26, 6,
[Week] <= 30, 7,
[Week] <= 34, 8,
[Week] <= 39, 9,
[Week] <= 43, 10,
[Week] <= 47, 11,
[Week] <= 52, 12
)
),
"Month", FORMAT ( DATE ( 2020, [MonthNum], 1 ), "MMMM" )
)
RETURN
vAddMonthPat
- APM
So I'm back to this setup, and I have been able to use the TODAY function to get this year's data and last year's data using a DAX formula.
However, I'd really like to just have all the sales data flow in and then use a slicer to toggle between:- Today
- Last Week
- Month-To-Date
Such that when I select "Today" in the slicer, sales data for today and the comparable day last year (as per above calendar) come up - and the same for last week and month-to-date (with comparable last year data for each).
Does anyone know how to do this?
Thanks!
- mahoneypat
APM You can start your calendar on DATE(2019,12,29) and use this column expression instead. I'll think about the Month issue, but it should be doable.
Day 2 =
VAR vThisYear = 'Date 445'[Year]
VAR vMaxSunday =
CALCULATE (
MAX ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vPYMaxSunday =
CALCULATE (
MAX ( 'Date 445'[Date] ),
ALL ( 'Date 445' ),
'Date 445'[Year] = vThisYear - 1,
'Date 445'[DayOfWeek] = "Sunday"
)
VAR vDayThisYear =
DATEDIFF ( vMaxSunday, 'Date 445'[Date], DAY ) + 1
VAR vDayPrevYear =
DATEDIFF ( vPYMaxSunday, 'Date 445'[Date], DAY ) + 1
VAR vResult =
IF ( [Date] >= vMaxSunday, vDayThisYear, vDayPrevYear )
RETURN
vResultPat