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.
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?
Thanks for the response. Is there a DAX calculation that anyone is aware of that I can use to calculate an "adjusted" date table or something? I can't imagine that doing this is an impossible feat. Perhaps within the visualization restrcitions of Power BI but probably not in a tool like Tableau. Surely I can't be the only one who's ever had to visualize Y/Y metrics at daily granularity.
To me it would be something in the realm of a calculated measure like this:
But instead of SAMEPERIODLASTYEAR, it would be a more custom DAX formula for "same period last year, -1 day"...or something. Something that calculate's the measure value for the selected period but shifts the value for one day prior.