Forum Discussion
DAX Calculating Status Duration and Averages
Hi,
I am looking for some input on calculating status duration and averages. We have a table that takes a snapshot of each serial no. daily and what status it is in. Currently 50 million + rows.
I need to calculate the time each Serial No. has spent in it's current status. I started with creating the measure below but the end date is not calculating correctly because a serial can be in the same status multiple times throughout it's life. I am not sure how to index the events:
StatusDuration =
VAR StartDate = CALCULATE(
FIRSTNONBLANK('Fixed Asset History'[Date of Record], TRUE),
ALLEXCEPT('Fixed Asset History','Fixed Asset History'[Serial No.],'Fixed Asset History'[Status])
)
VAR EndDate = CALCULATE(
LASTNONBLANK('Fixed Asset History'[Date of Record], TRUE),
ALLEXCEPT('Fixed Asset History','Fixed Asset History'[Serial No.],'Fixed Asset History'[Status])
)
RETURN
IF( StartDate = EndDate,
(DATEDIFF(StartDate, TODAY(), DAY)),
DATEDIFF(StartDate, EndDate, DAY)
) + 1
Lastly, I'd like the calculate the average time spent in all statuses once I correctly calculate duration. Would it be best to do a measure for each status?
Thank you in advance!
5 Replies
Try this column
Column = VAR PR = TOPN ( 1, FILTER ( 'Fixed Asset History', [Serial No] = EARLIER ( [Serial No] ) && [Status] = EARLIER ( [Status] ) && [Date of Record] < EARLIER ( [Date of Record] ) ), [Date of Record], ASC ) VAR Pdate = MINX ( PR, [Date of Record] ) RETURN IF ( ISBLANK ( PDate ), 1, DATEDIFF ( Pdate, [Date of Record], DAY ) + 1 )Hi Zubair,
Thank you for your reply. I tried the calculated column but was unsuccesful:
