Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
Works perfectly. Thanks!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |