Average days between dates within certain criteria

[Anonymous]

I have tried to piece this together from some similar posts but haven't gotten all the way there. I need a measure that calculates average number of days between dates in a column, but only account for dates within the last 90 days AND rows where the value in another column is not yellow. In addition, I need the average equation to include the number of days between today's date and the most recent qualifying date in the column. Note: the dates will not be in chronilogical order and there will be a changing number of rows.

Example: Here is my data

Date	Color
6/10/2019	Red
7/3/2019	Red
7/5/2019	Yellow
7/7/2019	Yellow
7/12/2019	Red
8/1/2019	Red
9/12/2019	Red

Today is 9/13/2019. I want it to ignore the record from 6/10 (not within the past 90 days) and the ones from 7/5 and 7/7 (color is "Yellow"). So the average equation would be (9 (date diff 7/3 and 7/12 + 20 (date diff 7/12 and 8/1) + 42 (date diff 8/1 and 9/12) + 1 (date diff 9/12 and today, 9/13)) / 4 = 18

[Greg_Deckler · Accepted solution]

So you want this then?

Measure = 
VAR __today = TODAY()
VAR __target = TODAY() - 90
VAR __table = FILTER('Table',[Date] >= __target && [Color] <> "Yellow")
VAR __table1 = ADDCOLUMNS(__table,"__previous",MAXX(FILTER(__table,[Date]<EARLIER([Date])),[Date]))
VAR __table2 = ADDCOLUMNS(__table1,"__days",IF(ISBLANK([__previous]),BLANK(),[Date] - [__previous])*1.)
VAR __max = MAXX(FILTER('Table',[Date]<__today),[Date])
VAR __days = (__today - __max) * 1.
RETURN
DIVIDE(SUMX(__table2,[__days]) + __days,COUNTROWS(__table2),BLANK())

Honestly, have to shake my head and wonder where you guys come up with these crazy calculation scenarios!! 🙂

Attached PBIX

[Anonymous]

Works perfectly. Thanks!