Count based on two date columns.

[jwin2424]

Alright. I am really hoping someone can help here. 

I am trying to provide a conversion rate for my opportunities. 

I have two seperate visuals here giving me the correct results. My issue is that I have 4 date columns here. 
[Estimated Close Year]

[Estimated Close QTR]

[Actual Close Year]

[Actual Close QTR]

I need to get the conversion rate by taking what actually closed divided by what was estimated to close. Here is the result I need. 

Year  / Quarter	Expected to Close as Won	Actually Won	Accuracy
2024
1	144	114	79.17%
2	597	237	39.70%
3	328	32	9.76%
4	56

I do NOT have a relationship with a calendar table because I have too many date columns and there isnt one specific date column that would be appropriate to use as a primary key. I tried this, but it doesn't work:

Estimated Close Opps =

CALCULATE(

    DISTINCTCOUNT(Opportunity[OPP ID]),

    Opportunity[Estimated Close Year] = Opportunity[Actual Close Year] &&

    Opportunity[Estimated Close Quarter] = Opportunity[Actual Close Quarter]

)

I need the visual to have the year and quarter in the rows, but I need to select a date column to do that. If I choose the actuals, it still applies that as a filter and doesnt give me the correct result. Because the estimated value is based on the estimated dates and the actual value is based on the actual dates, I don't know how to create a measure that says 

Give me the count of the opportunities where the estimated close year and quarter equals the year and quarter in the row

Give me the count of the opportunities where the actual close year and quarter equal the year and quarter in the row

Then divide the actual by the estimated and give me the %

Hoping this makes sense and you can help! 

[ryan_mayu · Accepted solution]

@jwin2424 

you can create a dim date table. Then for more than one date column, only one column's relationship can be active. Others are inactive. If you want to make those inactive relationship to be active, you can try USERELATIONSHIP function.

https://learn.microsoft.com/en-us/dax/userelationship-function-dax?wt.mc_id=DP-MVP-5004616

[jwin2424]

I am going to mark this as the answer. I youtubed it, and this was a good explination of it. 

Using USERELATIONSHIP in DAX (youtube.com)

It was specific to the different dates as well. You got me in the right direction. Thank you!

[ryan_mayu]

you are welcome

[jwin2424]

I am still not sure how that will work in the visual. Which date would go in the rows? 

[ryan_mayu]

then could you pls provide some sample data?