cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
efroseroc
Regular Visitor

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.

1 ACCEPTED SOLUTION
edhans
Super User III
Super User III

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

1 REPLY 1
edhans
Super User III
Super User III

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Top Solution Authors
Top Kudoed Authors