Date filters

[hel64]

I have 2 fact tables one actuals with a granularity of daily and one targets with a granularity of monthly. Each is linked to a different Dim_Date table to maintain active relationships in the data model. Each date table has a slicer filter visual which respectively controls the actual and targets figures which both reside in a table visualisation. My question is 'Can the two date slicers be 'synched' so that when one of the date slicer months is changed the other date slicer month is automatically changed to the same month. eg so the May 2020 target figures appear with the May 2020 actual figures in the table?

[v-juanli-msft · Accepted solution]

Hi @hel64 

Two answers above provide useful suggestions, is your problem sloved?

If not, you could try:

assume you have two date table, one is like date1: 2020/5/1  2020/5/2

another is like date2: May 2020  June 2020

You could add column in date1 like:

[month_year]=format(date1[date],"Mmmm YYYY")

Then create a relationship as below:

Download my pbix to see more details

https://qiuyunus-my.sharepoint.com/:u:/g/personal/admin_qiuyunus_onmicrosoft_com/EbswFaut3TlOmgTk-u6...

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@hel64 , You can have month start or the month end date in month table using month. example

Month is in format - May-2020// Mark Data Type as date

Date = "01-"&[Month]

Month is in format - YYYY-mm

Date = Date(left([Month],4),right([Month],2),1)  // Mark Data Type as date

You can now join both tables to same date table

[edhans]

why do you have two date tables? Why not just one? The date table doesn't care that there is a FACT table with daily granularity and another FACT table with monthly granularity? Plus, you cannot mark both as Date Table, and you lose some of the benefits of a date table not marked as a date table.

I do not think you can sync the slicers the way you want. Sync Slicers functionality is for slicers on different pages of  your report to keep the same fields in sync, not different tables.