cancel
Showing results for
Did you mean:
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.

LeMarc

1 ACCEPTED SOLUTION

Accepted Solutions
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])

6 REPLIES 6
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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
Regular Visitor

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

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

## 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, please consider Accept it as the solution to help the other members find it more quickly
Learning resources: Power BI
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!

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])

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.

Announcements