Measure leveraging multiple date fields

[PhilC]

Hi, 

I have a table for projects that has various dates representing different status values. I am looking to create a Success Rate for all Applications in the past 12 months.

The Application and Outcome (Successful or Unsuccessful) need to both be within that 12 month time period.

The Project ID is duplicated in the table, so a DISTINCTCOUNT is used initially.
Then, due to having multiple Dates, am using USERELATIONSHIP to apply the non active connection with the Date table.

The 12 Month rolling count of Applications seems to be working ok, however I am not sure how to then calculate [# Applications with Outcomes] and [# Successful Applications] measures, which form the basis of the Success Rate.

They will be subsets of the [# Applications (12M)] measure, with Outcomes needing to use FIRST_OUTCOME_DATE and Successes using FIRST_SUCCESSFUL_DATE

Measures are below for reference

Projects table includes the following fields that are relevant
PROJECT_ID, FIRST_SUBMITTED_DATE, FIRST_OUTCOME_DATE, FIRST_SUCCESSFUL_DATE

It is the FIRST_OUTCOME_DATE and FIRST_SUCCESSFUL_DATE fields that provides dates needed to create the 2 measures needed:

[# Applications (12M) with Outcome]

[# Successful Applications]

=[# Projects]
DISTINCTCOUNT(Projects[PROJECT_ID])

[# Applications]
=CALCULATE([# Projects],
USERELATIONSHIP(Dates[Date],Projects[FIRST_SUBMITTED_DATE]))

[# Applications (12M)]
=CALCULATE([# Applications],
DATESBETWEEN(Dates[Date],
NEXTDAY(SAMEPERIODLASTYEAR(LASTDATE(Dates[Date]))),
LASTDATE(Dates[Date])
)
)

[# Applications (12M) with Outcome]
???

[# Successful Applications]
????

Thanks in Advance

[v-gizhi-msft]

Hi,

Could you please share some sample data and an expected result as screenshots?

This will let me help you further.

Thanks!

Best Regards,

Giotto

[PhilC]

Hi Giotto  @v-gizhi-msft ,

Thanks for the response.  As per most datesets, the actual data is confidential.  It will take a bit to pull together mocked up data and work through the desired outcomes. 

Was hoping someone might be able to point me in the right direction based on the information provided without data.

If no-one assists, will pull together a mockup and tag you when done.

Thanks again for your interest to assist.

Cheers

Phil

[v-gizhi-msft]

Hi,

Any good progress?

Best Regards,

Giotto