cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
lemarcfj Regular Visitor
Regular Visitor

Date 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

1 ACCEPTED SOLUTION

Accepted Solutions
johnmc Frequent Visitor
Frequent Visitor

Re: Date Table For Year over Year Calculations At The Daily Level.

Hi 

 

You'd create measures like this...

 

Revenue TY = SUM('RevenueTable'[Revenue])

 

Revenue LY = CALCULATE([Revenue TY],DATEADD('DateTable'[Date],-364,DAY))

 

 

 

View solution in original post

6 REPLIES 6
Community Support Team
Community Support Team

Re: Date Table For Year over Year Calculations At The Daily Level.

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.

WEEKDAY Function (DAX)

WEEKNUM Function (DAX)

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lemarcfj Regular Visitor
Regular Visitor

Re: Date Table For Year over Year Calculations At The Daily Level.

Thank 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? 

Community Support Team
Community Support Team

Re: Date Table For Year over Year Calculations At The Daily Level.

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

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.



For learning resources/Release notes, please visit: | |
lemarcfj Regular Visitor
Regular Visitor

Re: Date Table For Year over Year Calculations At The Daily Level.

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:

Revenue LY = Calculate(SUM('Revenue'), SAMEPERIODLASTYEAR(DateTable'Date"))

 

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. 

 

Any help would be greatly appreciated!

johnmc Frequent Visitor
Frequent Visitor

Re: Date Table For Year over Year Calculations At The Daily Level.

Hi 

 

You'd create measures like this...

 

Revenue TY = SUM('RevenueTable'[Revenue])

 

Revenue LY = CALCULATE([Revenue TY],DATEADD('DateTable'[Date],-364,DAY))

 

 

 

View solution in original post

lemarcfj Regular Visitor
Regular Visitor

Re: Date Table For Year over Year Calculations At The Daily Level.

This worked! Thank you!

 

I actually thought of this syntax before but shifted to 366 days instead of 364. I got an error that you can't go over 365. 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 482 members 4,678 guests
Please welcome our newest community members: