topic Re: Indexing dates of the year in DAX Commands and Tips

Indexing dates of the year

abeirne — Wed, 19 Jan 2022 19:32:42 GMT

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.

Re: Indexing dates of the year

ValtteriN — Wed, 19 Jan 2022 19:38:10 GMT

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!

Re: Indexing dates of the year

abeirne — Wed, 19 Jan 2022 19:44:03 GMT

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?

Re: Indexing dates of the year

ValtteriN — Wed, 19 Jan 2022 19:50:27 GMT

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.