Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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],"-"))
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYou 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],"-"))
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
99 | |
50 | |
19 | |
12 | |
11 |