How to use 2 different dates with 1 calendar table

[Anonymous]

Hi, 

So for example, I have 
2 types of dates

Forecasted Date	Actual Date	Value
1/2/20	1/2/20	1
3/2/20	2/2/20	2
3/2/20	4/2/20	3

I am creating a matrix table that shows the values according to the forecasted dates and the values shown are controlled by a toggle.

Toggle > 1. Forecasted date 
2. Actual Date

Because all this data is in a single query, I am unable to create 2 relationships of dates to a single calendar table and the way I have done is thus far is to split the 2 dates into 2 different queries and linking them seperately to 1 calendar table. 

However by doing so, I have to regroup filters which make it a hassle to do it twice, is there a more elegant way to do this based off 1 query?

[Nathalie_ITK_78]

Hi, 

I found a video to solve the problem. by using DAX. It is well functionning for my reports. 

https://www.youtube.com/watch?v=u8_aeLmXbVk

Hope it will be helpfull.  

KR, Nathalie

[v-juanli-msft]

Hi @Anonymous 

You could unpivot the "Forecasted date" and "actual date" columns in Transform data,  then you would get a column with "forecast date","actual date", then a column with dates.

Next, create a relationship between the dates above and your calendar table.

Best Regards

Maggie

[amitchandak · Accepted solution]

@Anonymous , you can join them to the same date table and use userelation.

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

Or this : https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in-power-bi