Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 f...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    7 years ago

    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

Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
7 years ago

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's avatar
    Anonymous
    Not applicable
    7 years ago

    Works perfectly. Thanks!