Hi, I have two existing queries that counts historical days between redress in and redress out (active days) - and one that counts days if on active redress today.
My problem is that my existing queries sums the active days, and place the result on each redress out date. This creates a challenge for me when adding this measure to a date chart to display e.g average active days per month. As you see in the snip below, the item have been active in 35 days, but the result is only showing in July, whilst I need it to display sum of active days for June and July.
Also, for the last measure, that counts days on redress now, it shows 137 days in October, but should be showing all active days the last months.
Here's my column and measures as is now:
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)
Measures:
_Status =
VAR __id = MAX (_ITH_All2[SERIAL_NO] )
VAR __date = CALCULATE ( MAX(_ITH_All2[Date Applied] ), ALLSELECTED ('_ITH_All2' ), _ITH_All2[SERIAL_NO] = __id )
RETURN
CALCULATE ( Min (_ITH_All2[Location Status] ), VALUES ('_ITH_All2'[SERIAL_NO] ),'_ITH_All2'[SERIAL_NO] = __id,_ITH_All2[Date Applied]= __date )
last_date =
VAR __id = MAX (_ITH_All2[SERIAL_NO] )
VAR __date = CALCULATE ( MAX(_ITH_All2[Date Applied] ), ALLSELECTED ('_ITH_All2' ), _ITH_All2[SERIAL_NO] = __id )
RETURN
CALCULATE ( Min (_ITH_All2[Date Applied] ), VALUES (_ITH_All2[SERIAL_NO] ),_ITH_All2[SERIAL_NO] = __id,_ITH_All2[Date Applied] = __date )
Days On Redress now = sumx(values(_ITH_All2[SERIAL_NO]),if([_Status] ="Redress In", datediff([last_date], today(), day), blank()))
Days on redress historical= Sum(DAYS ON REDRESS COL)
Days on Redress All= Days on redress historical + Days On Redress now
So, a measure to display sum of active days for each period, and a measure to display average days per period would be highly appreciated.
Thanks in advance!
