How to create a filter of date if I have multiple tables with different dates.

[Julio9704]

I have 4 tables with different dates each (the date format is mm/dd/yy), my guess is that I have to create a "date" dable, I did that with this code:

Date = SUMMARIZE(

UNION(

DISTINCT('Table1'[Date1]),

DISTINCT('Table2'[Date2]),

DISTINCT('Table3'[Date3]),

DISTINCT('Table4'[Date4])

),

Table1[Date1])

Thus, I have the Primary Key named Date in the table Date; so, I made the relation between 'Date'[Date] and 'Table2'[Date2] (I guess relating it like this is enough).

But, the thing is that I want to filter it by "month year", so, in Date, I created the following column:

Month Year = FORMAT('Date'[Date],"mmmm YYYY").

So far so good. When I try to do a slider using "Month Year", and I select, for example, from week 2 to week 51, I do not get the proper numbers in all of the other visualizations.

Is this a problem of relationships or did I do something wrong?

[V-lianl-msft]

Hi @Julio9704 ,

Use the calendar function to recreate the date table and create relationships with other tables.

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Julio9704 , Few things.

1) You should create a date table using Calendar and calendar auto

2) Month and week depending on format need sort column https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-sort-by-column

3) When Drill from week to a month or vice versa, other will not be complete

Month Year = FORMAT([Date],"mmmm yyyy")
Month Year sort = FORMAT([Date],"yyyymm")
Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
Set Sort Column
Year = FORMAT([Date],"YYYY" )

Wee can be sorted on week start date

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")

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 :radacad sqlbi My Video Series Appreciate your Kudos.