Forum Discussion

jwin2424's avatar
jwin2424
Resolver I
2 years ago
Solved

Count based on two date columns.

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  / QuarterExpected to Close as WonActually WonAccuracy
2024   
114411479.17%
259723739.70%
3328329.76%
456  


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! 

5 Replies