How to have a common date slicer for three date fields in the same table

[Anonymous]

Hello Team,

How to have a common date slicer for three date fields in the same table.

The Year slicer can filter MOTREF1,MOTREF2 and MOTREF3. 

Any suggestions? 

Thanks

[v-yetao1-msft]

Hi @Anonymous 

What is the result, can you achieve your needs?

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

Best Regards

Community Support Team _ Ailsa Tao

[v-yetao1-msft · Accepted solution]

Hi @Anonymous 

You can create a calculated column in Calendar table . Use FORMAT dax to convert the date format into year and month format, and then add a slicer, put this column in the slicer, keep the original relationships, no need to change, the data in the data table will be changed according to your filtering .

I made a little change to the original data for easy comparison .

Calculated column : Column = FORMAT('Calendar date'[Date],"mmm-yyyy")

The effect is as shown

The final result is as shown

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Thanks a lot! 

[Anonymous]

Amazing and thanks! Will be right back after trying!!!

[v-yetao1-msft]

Hi @Anonymous 

You need create a calendar table first and then create relationships between the calendar date and three dates in data table .

(1)Create a calendar table like this, and use the field as a slicer .

Calendar date = CALENDAR(DATE(2021,06,01),DATE(2021,06,10))

(2)create relationships between the calendar date and three dates in data table.

Create an active relationship between 'Table'[MOTREF1] and 'Calendar date'[Date], two inactive relationships between 'Table'[MOTREF2] and 'Calendar date'[Date] , 'Table'[MOTREF3] and 'Calendar date'[Date] .

Then create measures by USERELATIONSHIP DAX to activate the inactive relationship . But there is one thing to note: Inactive relationships will only be activated when measure is called in visual.

(3)Create measures with USERELATIONSHIP dax .

MOTREF2 = CALCULATE(SUM('Table'[Value]),USERELATIONSHIP('Table'[MOTREF2],'Calendar date'[Date]))

MOTREF3 = CALCULATE(SUM('Table'[Value]),USERELATIONSHIP('Table'[MOTREF3],'Calendar date'[Date]))

(4)Add the measure in visual then the relationships can be activated .

The effect is as shown

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[Anonymous]

Hi Ailsa, 

Is there a way to have Year and months rather than individual dates ? 

Thanks

[Anonymous]

Thanks  a lot ! Will try and be right back.

[Anonymous]

Hello Amit, 

I have created a Calendar table :

Not sure what to do next in terms of relating the [Date] : CalendarAuto [Date] and MOTREF1,MOTREF2,MOTREF3

Thanks

[Anonymous]

Thanks, Amit, will try and be right back. 

[amitchandak]

@Anonymous , join them with a common date table. Only one join will be active. rest you can activate using userelationship

refer my blog for more details

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970