Forum Discussion

MLH4's avatar
MLH4
Frequent Visitor
2 years ago
Solved

Days Between - Days Since DAX

I have been trying to show days between injury dates. I was able to accomplish this task. However, the most recent "Days Between" data point will never be accurate since another injury has yet to occur. 

 

I have also created a "Days Since" measure to show how many days since the last injury. 

 

My question is, how can I have a measure show days between injury unless it is the most recent injury(unable to show days between). So, I am needing a days between previous injuries along with a days since count for the most recent datapoint. 

 

Below are the current dax formulas for each measure:

 

DaysBetweenSteps = VAR IndexNum = Max(Safety[Index]) VAR PreviousIndexNum = CALCULATE(MAX(Safety[Index]), FILTER(ALLSELECTED(Safety), Safety[Index] < IndexNum)) VAR CurrentDate = VALUE(SELECTEDVALUE(Safety[ACTUALINCIDENTDATE])) VAR PriorDate = VALUE(CALCULATE(SELECTEDVALUE(Safety[ACTUALINCIDENTDATE]), FILTER(ALL(Safety), Safety[Index] = PreviousIndexNum)))  RETURN  IF(IndexNum= CALCULATE(MIN(Safety[Index]), ALLSELECTED(Safety)), 0, CurrentDate - PriorDate)
 
 
Days Since = if(LASTDATE(Safety[Date])=blank(),blank(),(1.*(today() - LASTDATE(Safety[Date])-1)))
 
 
The image below shows the current chart along with the desired outcome(In red):

 

 
  • However, the most recent "Days Between" data point will never be accurate since another injury has yet to occur. 

     

    Read about COALESCE  and TODAY()

3 Replies