Combining two date cells with measures

[Matt Cass]

Hi,

I'm struggling to get this to work, any solutions would be much appreciated.

- data shows forecasted and actual sales for the year.
- data has two date cells, 'projected close date' and 'actual close date'. i.e. when we think it's going to land and when it actually landed.
- want to create a pivot chart and pivot table with one common date field. i.e. to show forecast and actual figures per month and overlay forecast and actual over each other on a chart.
- I've been trying to get measures to work with a USERELATIONSHIP but not sure on correct formula

= CALCULATE (

    data[Projected closing date],

    USERELATIONSHIP ( data[Actual closing date], 'Calendar'[Date] )

)

Thanks!

 

[Matt Cass]

Hi,

So I've tried a multitude of alternatives.

The closest I got to a solution was using power query and unpivoting the date fields which worked but then the problem was I was getting duplicate values as it was not differentiating the forecast and actual values, so I was getting duplicate values.

I've now simplified and tried to build using different pivot tables but I can't find a way to merge these two charts. Can anyone help?

My end goal with this is to have the forecast sale, actual sale, forecasted land date and actual land date to be overlapped on one chart, so I can see the forecasted sales and actual sales over the year.

Once I've got that working I'm then going to create a further chart that splits the forecasted value between different stages of the 'pipeline stage' cell.

I can upload the file if that's allowed/possible?

Many thanks

Matt