Counting historical days in active status + days in active status today

[Ken_Jorp]

Hi, 

I have created a calculated column that counts number of days a specific serial number has been in a specific status, "Redress". 

This works pretty good, but I have encountered a challenge: As the DAX only calculates days BETWEEN "Redress In" and Redress Out"  it will fail to count the days for the serial numbers which are in "Redress In" status today.

So what I need is a way to include a calculation in my current DAX, that counts the days since each serial went into "Redress In" status - Where there are no "Redress Out" after "Redress In"  for the specific serial number.

Here is my current DAX:

DAYS ON REDRESS COL =
VAR myindex = CALCULATE(MAX (_ITH_All2[Index]),_ITH_All2[Location Status]="Redress Out")

VAR myserial = _ITH_All2[SERIAL_NO]

VAR previousindex =
CALCULATE (
MAX ( _ITH_All2[Index] ),_ITH_All2[Location Status]="Redress In",
FILTER ( _ITH_All2,_ITH_All2[SERIAL_NO] = myserial
&& _ITH_All2[Index] < myindex)
)

VAR previousdate =
CALCULATE (
MAX ( _ITH_All2[Date Applied] ),
FILTER (
_ITH_All2,
_ITH_All2[Index] = previousindex
&& _ITH_All2[SERIAL_NO] = myserial
)
)

RETURN
IF ( previousdate , _ITH_All2[Date Applied] - previousdate)

Which gives me this data, but as you see, this serial number is currently in "Redress In" status, so I would like to count the days from "Redress in" to TODAY. 

Thanks in advance.

[amitchandak · Accepted solution]

@Ken_Jorp , Create these three measures and try

_date =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[status] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )

_status =
VAR __id = MAX ('Table'[ID] )
VAR __date = CALCULATE ( MAX('Table'[date applied] ), ALLSELECTED ('Table' ), 'Table'[ID] = __id )
CALCULATE ( Min ('Table'[date applied] ), VALUES ('Table'[ID] ),'Table'[ID] = __id,'Table'[date applied] = __date )

diff = if([Status] ="Redress In", datediff(_date, today(), day), blank())

diff = sumx(values(Table[ID]),if([Status] ="Redress In", datediff(_date, today(), day), blank()))

[Ken_Jorp]

@amitchandak Thanks! That certainly did the trick:)