Indexing dates of the year

[abeirne]

Hi, I would like to index the dates of each year (2019, 2020, 2021) so that I can match the index to the previous year's date.

So 2019 would have the index on the dates, and then I could match the 2020 index to the 2019 dates. Kind of like SamePeriodLastYear, but I am not able to use this with my current data, as the original ID's are all different throughout the years. Please help, I can explain more if needed. I am looking at quotes sent throughout the years, and would like to compare then YoY, but cannot do so without being able to connect the quotes to a primary key. I was thinking the index would become the primary key. Thank you in advance.

[ValtteriN · Accepted solution]

Hi,

I am not sure if I follow, what you are describing would be day of year? You can get day of year with this calculated column dax: 

DayOfYear = DATEDIFF(STARTOFYEAR('Calendar'[Date]),'Calendar'[Date],DAY)+1

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[abeirne]

Thank you! Will this work year over year? So I would like a 1-365 from 2019, but also 1-365 to 2020, and so on. Would I need seperate date tables for this?

[ValtteriN]

You would need separated table or some other logic to address leap years. The problem is that leap years will have numbers from 1-366.