Getting Running Time (from subtracting Previous row) using DAX

[Anonymous]

Hello All,

I need to get Cumulative Running Time of running pumps using DAX

I have created 3 measures for that,
First for getting Previous row,

Second for subtracting the current row from previous row,

Third to get cumulative runnning time

But I am facing a small issue, I have explained my issue below the screenshot

Here are the formula for Measures:

Prev_value_run_time =

VAR Index = [Index]

VAR Prev_datetime =

    MAXX (

        FILTER (

            ALLSELECTED ( FloatTable ),

            FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )

                && FloatTable[Time] < SELECTEDVALUE ( FloatTable[Time] )

        ),

        FloatTable[Time]

    )

VAR Prev_date =

    MAXX (

        FILTER (

            ALLSELECTED ( FloatTable ),

            FloatTable[Date] < SELECTEDVALUE ( FloatTable[Date] )

        ),

        FloatTable[Date]

    )

VAR Prev_time =

    MAXX (

        FILTER ( ALLSELECTED ( FloatTable ), FloatTable[Date] = Prev_date ),

        FloatTable[Time]

    )

RETURN

    SWITCH (

        TRUE (),

        NOT ISBLANK ( Prev_datetime ),

            CALCULATE (

                SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Time] ),

                FILTER (

                    ALLSELECTED ( FloatTable ),

                    FloatTable[Time] = Prev_datetime

                        && FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )

                        && FloatTable[TagIndex] = Index

                )

            ),

        CALCULATE (

            SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Time] ),

            FILTER (

                ALLSELECTED ( FloatTable ),

                FloatTable[Time] = Prev_time

                    && FloatTable[Date] = Prev_date

                    && FloatTable[TagIndex] = Index

            )

        )

    )                                        // Here I have included Index column because I need to do group by based on Tag Index

run time =

SWITCH(SELECTEDVALUE(FloatTable[pump_flag]), 1, [Time Sum] - [Prev_value_run_time])*1440        // [Time sum] is the SUM Time

run time1 =

SWITCH(SELECTEDVALUE(FloatTable[pump_flag]), 1, CALCULATE( [Run time sum],  FILTER( ALLSELECTED(FloatTable), FloatTable[Time] <= MAX(FloatTable[Time]))))                    // [Run time sum] is the SUM of  measure [run time]

Screen Shot:

So in the sceenshot you can see that in Time column, Difference between 2 consecutive rows is 10 mins.  In the column run time1 we can see the cumulative running time, it is working fine but at some point of time when Difference b/w 2 consecutinve rows is greater than 10 mins there it is showing incorrect value, I need to fix this.

Can anyone help me in this issue?

If you've any question feel free to ask.

Thanks and Regards
Mihir

[Anonymous]

Hi @Anonymous ,

Could you please provide a pbix file without privacy inforamtion and desired output with more details? What the desired output in the red mark?

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.