Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

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

 

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

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

2 REPLIES 2
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Works perfectly. Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.