cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Resolver I

## 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  / 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!

1 ACCEPTED SOLUTION
Super User

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

Proud to be a Super User!

5 REPLIES 5
Super User

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

Proud to be a Super User!

Resolver I

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

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

Super User

you are welcome

Proud to be a Super User!

Resolver I

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

Super User

then could you pls provide some sample data?

Proud to be a Super User!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.