Forum Discussion
MLH4
2 years agoFrequent Visitor
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()