Working with date columns

[Anonymous]

Hi, 

Im having difficulties with a simple task: I several sets of data with date values. I want to be able to create a report where for example a table containing sales and another table containing costs are filtered by year, quarter, month etc. 

Creating columns for all these tables would be silly. Should I make a separate "date table" that filters other tables or how should i do this?

Thanks for the help! 

[amitchandak · Accepted solution]

@Anonymous , yes you should create a date table. and join all your dates with those tables.

If one table has more than one date, then only one will be active. Other you can activate using, userelation.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

userelation - only when you have more than one date in table 
https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Appreciate your Kudos.

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

I think these blogs will help you.

Do You Need a Date Dimension?

Power BI Date Dimension; Default or Custom? Is It Confusing? 

And you could reference these ones to learn how to create a date table.

Using a Date Dimension Table in Power BI 

[amitchandak · Accepted solution]

@Anonymous , yes you should create a date table. and join all your dates with those tables.

If one table has more than one date, then only one will be active. Other you can activate using, userelation.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184

userelation - only when you have more than one date in table 
https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi

Appreciate your Kudos.