DAX Calculating Status Duration and Averages

[RDA_PowerBI]

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! 

[Zubair_Muhammad]

@RDA_PowerBI

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 )

[RDA_PowerBI]

Hi Zubair, 

Thank you for your reply. I tried the calculated column but was unsuccesful: 

[Zubair_Muhammad · Accepted solution]

Hi @RDA_PowerBI

It works for me both in Excel and Power BI desktop

I am attaching both files.

[Zubair_Muhammad]

Heres the Excel file. See the Power Pivot Model

[Zubair_Muhammad]

@RDA_PowerBI