Two different date columns on one chart

[LauraCawdronDav]

Hi,

I’m not sure this is even possible but thought this would be the best place to ask! I’ve been asked to create a chart which shows months this year along the x axis and value of opportunities on the y axis.

I’m looking for the chart to show us when opportunities closed as ‘won’ were closed and when we can expect revenue throughout the rest of the year.

We have two different date columns related to our opportunities: when the opportunity is expected to close and when the opportunity is actually closed. Therefore, in April I would like the chart to show what was won in January, February and March, what has been won and is expected to close in April, and what is expected to close in May, June, July etc. I have provided a mock example below.

Is this possible with DAX or is there a visualisation I can use to help me use two columns of data in one visual depending on the date?

Thanks!

[amitchandak · Accepted solution]

@LauraCawdronDav , With a common date table with active inactive joins and measures like. Refer examples

Expected  = CALCULATE(COUNT(Table[Opportunity Id ]),USERELATIONSHIP(Table[expected close Date],'Date'[Date]) )

Won  = CALCULATE(COUNT(Table[Opportunity Id ]),USERELATIONSHIP(Table[Won Date],'Date'[Date]),not(ISBLANK(Employee[Won Date])))

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

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