Advice managing dates

[efroseroc]

Hi thanks for reading.

I have 2 data sources which I need to relate through dates.

I  was wondering what were the disadvantages to instead of making a regular date table using the CALENDAR function for each day of the year,  adjusting both data sources date field to (Feb-21) for example, and  relating them through a table that has Jan-21, Feb 21.....

Is there any reason to not managing the dates this way?

Thanks again,

Best regards,

E.

[edhans · Accepted solution]

You need a true date table, not Jan-21, Feb-21, etc. I use this date table. It has a Month Year field that is Jan-21, Feb-21 that you can use in visuals, but the table is a true date table with all dates from start to finish in the Date[Date] field. This one starts Jan 1, 2016, but you can change the source line of it. Just paste the M code (full directions at the link) into a blank query. You then use the Month Year Sort field to sort it.

Then relate all fact tables to the Date[Date] field. Even if your fact tables dont' ahve a full date, make one in Power Query. If it is Jan-21, you can use the formula below to convert hat to Jan 1, 2021. Now you have a true date field to relate.

Date.FromText(Text.BeforeDelimiter([Column1],"-") & " 1, 20" & Text.AfterDelimiter([Column1],"-"))