Filter two tables with the same dates slicers

[Anonymous]

Hello,

We have two tables, they both use the column years, month and day separately and they all repeat multiple times. The issue we have, is that we have to use both tables in the same page and I need a date filter (year, then month, then day) that works for both tables, this is how the tables look:

Table 1:

Year	Month	Finish Date	Revenue	Target
2020	Jun	19th	xxxxxxx	xxxxxx
2020	Jun	12th	xxxxxxx	xxxxxx
2020	Jul	30th	xxxxxxx	xxxxxx
2020	May	19th	xxxxxxx	xxxxxx

Table 2:

Year	Month	Finish Date	TR	Other columns
2020	Jul	30th	xxxxx	xxxxx
2020	Aug	12th	xxxxx	xxxxx
2020	Jan	12th	xxxxx	xxxxx
2020	May	19th	xxxxx	xxxxx

Right now, because of how they used to have it on another platform, they had a filter for year, then for months and then that reduced the options in the finish date filter and with those 3 they filtered both tables (I mean the graphs out of them).

We need ideas on how to recreate that. As you can see, year, month and finish date can repeat multiple times (even though in finish date we might have 2 19th but of different months), so right now we don't have a way to filter both tables just like that or to make relationships between both tables as to make an slicer for both. I could have 6 slicers (year, month and finish date each twice) for both tables but that isn't what the user wants at all. 

What can we do?

TIA

[amitchandak · Accepted solution]

@Anonymous , you can create date

date = left([Finish Date],2) & "-" & [Month] & "-" & [Year]

and join with date table. and if needed create a common dimension.  and analyze together

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


Appreciate your Kudos.