Forum Discussion
Advice managing dates
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.
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],"-"))
1 Reply
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],"-"))