Getting Running Time (from subtracting Previous row) using DAX

Question

Hello All,&nbsp;I need to get Cumulative Running Time of running pumps using DAXI 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&nbsp;But I am facing a small issue, I have explained my issue below the screenshot&nbsp;Here are the formula for Measures:&nbsp;Prev_value_run_time = VAR Index = [Index]VAR Prev_datetime =&nbsp; &nbsp; MAXX (&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( FloatTable ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; FloatTable[Time] &lt; SELECTEDVALUE ( FloatTable[Time] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Time]&nbsp; &nbsp; )VAR Prev_date =&nbsp; &nbsp; MAXX (&nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( FloatTable ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Date] &lt; SELECTEDVALUE ( FloatTable[Date] )&nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Date]&nbsp; &nbsp; )VAR Prev_time =&nbsp; &nbsp; MAXX (&nbsp; &nbsp; &nbsp; &nbsp; FILTER ( ALLSELECTED ( FloatTable ), FloatTable[Date] = Prev_date ),&nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Time]&nbsp; &nbsp; )RETURN&nbsp; &nbsp; SWITCH (&nbsp; &nbsp; &nbsp; &nbsp; TRUE (),&nbsp; &nbsp; &nbsp; &nbsp; NOT ISBLANK ( Prev_datetime ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Time] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( FloatTable ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Time] = Prev_datetime&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; FloatTable[Date] = SELECTEDVALUE ( FloatTable[Date] )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; FloatTable[TagIndex] = Index&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ),&nbsp; &nbsp; &nbsp; &nbsp; CALCULATE (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SUMX ( FloatTable, VAR Index = FloatTable[TagIndex] RETURN FloatTable[Time] ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FILTER (&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ALLSELECTED ( FloatTable ),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FloatTable[Time] = Prev_time&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; FloatTable[Date] = Prev_date&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &amp;&amp; FloatTable[TagIndex] = Index&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; )&nbsp; &nbsp; )&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // Here I have included Index column because I need to do group by based on Tag Index&nbsp;run time = SWITCH(SELECTEDVALUE(FloatTable[pump_flag]), 1, [Time Sum] - [Prev_value_run_time])*1440&nbsp; &nbsp; &nbsp; &nbsp; // [Time sum] is the SUM Time&nbsp;run time1 = SWITCH(SELECTEDVALUE(FloatTable[pump_flag]), 1, CALCULATE( [Run time sum], &nbsp;FILTER( ALLSELECTED(FloatTable), FloatTable[Time] &lt;= MAX(FloatTable[Time]))))&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // [Run time sum] is the SUM of&nbsp; measure [run time]&nbsp;&nbsp;Screen Shot:So in the sceenshot you can see that in Time column, Difference between 2 consecutive rows is 10 mins.&nbsp; 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.&nbsp;Can anyone help me in this issue?&nbsp;If you've any question feel free to ask.&nbsp;Thanks and RegardsMihir

anonymous · Answer

Hi&nbsp;Anonymous&nbsp;,
Could you please provide a pbix file without privacy inforamtion and desired output with more details? What the desired output in the red mark?
&nbsp;
Best Regards
Community Support Team _ Polly
&nbsp;
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Getting Running Time (from subtracting Previous row) using DAX

1 Reply

Related content

Subtraction calulation

DAX getting previous value

Subtract previous days using M-Query

get value from Previous row

Getting value for previous year based on value name and year slicer