Forum Discussion
Helper IDate Table For Year over Year Calculations At The Daily Level.
Hi Everyone,
Because of the seasonality of the business that I work at, I am tasked with visualization and analyzing data at daily granularity. That said, I often times have to make sure that all visuals are compared year over year so that the day of the week matches the same day of the week last year. For example, Monday August 20, 2018 is Sunday August 20, 2017 - so comparing these two days on a visual wouldn't be accurate. I would need to compare to Monday August 21, 2017 instead.
I'm wondering, how can I create a date table that will dynamically account for shifts in days that occur each year. I've never had to visualize this granular so its new to me. Ideally, I could find a solution that works dynamically with a day and week slicer.
Thanks in advance!
LeMarc
Hi
You'd create measures like this...
Revenue TY = SUM('RevenueTable'[Revenue])
Revenue LY = CALCULATE([Revenue TY],DATEADD('DateTable'[Date],-364,DAY))
6 Replies
Hi lemarcfj,
>>For example, Monday August 20, 2018 is Sunday August 20, 2017 - so comparing these two days on a visual wouldn't be accurate. I would need to compare to Monday August 21, 2017 instead.
It is hard to find out fully correspond records between two years.(start part and end part of year are hard to matched)
>> I've never had to visualize this granular so its new to me. Ideally, I could find a solution that works dynamically with a day and week slicer.
You can add weeknum and weekday to your calendar so you can simply lookup related records between different years.
Regards,
Xiaoxin Sheng
lemarcfjThank you for your reply.
It is hard to find out fully correspond records between two years.(start part and end part of year are hard to matched)
Does this mean that there is no good way to configure a date table to account for this without manually shifting dates in the date table? I'm assuming that we are not the only company that looks to visualize Y/Y performance at daily granularity. Have you ever heard of thethe visualization/data model best practice to achieve this?
HI lemarcfj,
Nope, I mean 'day of week' are dynamic between years, so it is hard to compare both day level and 'day of week' level at same time.
For example:
This year is start at Monday, previous year is start at Sunday and 2016 is started at Friday. How did you fully match them?For this scenario, we will ignore some of conditionals to help us calculate more simply.
Regards,
Xiaoxin Sheng
