Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
General Comment
Need Help
  • edhans's avatar
    edhans
    5 years ago

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

     

edhans's avatar
edhans
Community Champion
5 years ago

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