cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
miiihiir
Helper I
Helper I

Getting Running Time (from subtracting Previous row) using DAX

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:

miiihiir_0-1665066533980.png

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

1 REPLY 1
v-polly-msft
Community Support
Community Support

Hi @miiihiir ,

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.

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.

Top Solution Authors
Top Kudoed Authors