To Find the earliest date of a measure when running total crosses a threshold

[sarveshkalyan]

Hi,

I am faced with a challenge of getting the earliest date of a measure in powerbi, since it is a measure i am not able to proceed without column references.

This is a sample dataset,

Apps	Total	Count	Running Count	Percent Covered	Target Reached(>85%)	Date	Earliest Date
111	60	47	47	78%	No	10/12/2021	15/12/2021
111	60	3	50	83%	No	13/12/2021	15/12/2021
111	60	9	59	98%	Yes	15/12/2021	15/12/2021
111	60	1	60	100%	Yes	17/12/2021	15/12/2021
124	34	27	27	79%	No	19/12/2021	21/12/2021
124	34	3	30	88%	Yes	21/12/2021	21/12/2021
124	34	2	32	94%	Yes	23/12/2021	21/12/2021

Here, except for App Code, Count, Date, all the other fields are measures. Earliest Date is the field that i wish to calculate

Earliest date is the first date when a running total count specific to an app crosses the 85% threshold.

With the following measure i am able to get multiple dates, but i am not able to apply FIRSTDAY, RANKX OR TOPN to get the 1st output without a column reference

Date after 85% =

IF('App Progress'[Percent Covered]>=0.85,

    MINX('App Progress','App Progress'[Date]) ,

BLANK())

The following measure gives me just the 1st day in each app, i want to modify this to include the threshold >85% to get the expected value

Earliest Date =

    VAR _App = MIN('App Progress'[Apps])

    RETURN

MINX( FILTER(ALL('App Progress'), 'App Progress'[Apps] = _App),'App Progress'[Date])

Please let me know on how to proceed

[sarveshkalyan]

Thanks, you are right. Calculated columns helped me to solve this 

[lbendlin · Accepted solution]

Does it have to be measures?  These facts seem to be immutable (not impacted by user filter choices) so calculated columns should be sufficient.

Percent Covered = 
var a = [Apps]
var d = [Date]
return divide(calculate(sum('App Progress'[Count]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Date]<=d),[Total])

Earliest Date = 
var a = [Apps]
return calculate(minx('App Progress','App Progress'[Date]),ALL('App Progress'),'App Progress'[Apps]=a,'App Progress'[Percent Covered]>=0.85)

see attached.