Forum Discussion

RDA_PowerBI's avatar
RDA_PowerBI
Frequent Visitor
8 years ago
Solved

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. ...
Need Help
Zubair_Muhammad's avatar
Zubair_Muhammad
Icon for Community Champion rankCommunity Champion
8 years ago

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 )