Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I am trying to create a date table for YOY comparisons.
This is seemingly simple, but it is complicated by two factors:
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!
Solved! Go to Solution.
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
vResult
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
vAddMonth
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
@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
vResult
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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
vResult
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
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:
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!
Hey @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?
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
vAddMonth
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |