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
