DAX Formula - Calculate Day Number based on # of Claims

[tgibble]

Hello Power BI Community,

I have a set of data that tracks the number of claims by date for 2 sites.  I am looking for a DAX formal that calculates the first day the total number clams reach 5 for a given site, then counts on from there.  See the example of he the dataset below.  For site A the first day was 3/3/20, and day 2 was 3/4/20.  For site B, day 1 was 3/2/20, day 2 was 3/3/20, and day 4 was 3/4/2020.    

Date	Site	# of Claims	Day
3/1/20	Site A	0	0
3/1/20	Site B	0	0
3/2/20	Site A	3	0
3/2/20	Site B	5	1
3/3/20	Site A	5	1
3/3/20	Site B	6	2
3/4/20	Site A	8	2
3/4/20	Site B	9	3

Thanks,

Tim

[az38 · Accepted solution]

Hi @tgibble 

try a measure

Day = 
var _first5 = CALCULATE(MIN('Table'[Date]),ALLEXCEPT('Table','Table'[Site]),'Table'[# of Claims]=5)
return
CALCULATE(COUNTROWS('Table'),ALLEXCEPT('Table','Table'[Site]),DATESBETWEEN('Table'[Date],_first5,SELECTEDVALUE('Table'[Date])))

[tgibble]

Thanks, @az38.  This worked!!!!