Forum Discussion
Average days between dates within certain criteria
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
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
2 Replies
- Greg_Deckler
Community Champion
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