Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
lemarcfj
Helper I
Helper I

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

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
v-shex-msft
Community Support
Community Support

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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? 

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
If this post helps, please consider accept as solution to help other members find it more quickly.

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!

Hi 

 

You'd create measures like this...

 

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

 

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

 

 

 

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.