Calculate Previous Minute Values

[Anonymous]

Hi All,

I have a table which contains current and voltages values for each 3 minutes of intervel.

I would like to calculate the previous minute value from the current minute value.

Sample data looks as like below table.

KEYTimeVALUE

Input Current L1	11:00:00 AM	20
Input Current L1	11:03:00 AM	34
Input Current L1	11:06:00 AM	43
Input Current L1	11:09:00 AM	58
Input Current L1	11:12:00 AM	53
Input Voltage L1	11:00:00 AM	230
Input Voltage L1	11:03:00 AM	145
Input Voltage L1	11:06:00 AM	170
Input Voltage L1	11:09:00 AM	240
Input Voltage L1	11:12:00 AM	280
Output Voltage L1	11:00:00 AM	230
Output Voltage L1	11:03:00 AM	233
Output Voltage L1	11:06:00 AM	232
Output Voltage L1	11:09:00 AM	231
Output Voltage L1	11:12:00 AM	230
Output Current L1	11:00:00 AM	23
Output Current L1	11:03:00 AM	24
Output Current L1	11:06:00 AM	25
Output Current L1	11:09:00 AM	25
Output Current L1	11:12:00 AM	26

I tried the below formula to get the previous minute 

Previous Minute = MAXX(FILTER(ALL(Sheet2),[Time]<EARLIER([Time])),[Time])

And i got the previous minutes.

I tried to get the previous values using the same method but i couldnt. i am getting the wrong values.

Previous Value = 
VAR pre = MAXX(FILTER(ALL(Sheet2),[Time]<EARLIER([Time])),[Time])
RETURN 
MINX(FILTER(ALL(Sheet2),Sheet2[Time]=pre),[VALUE])

Output:-

 

Can any one please correct me..

Thank you.

Mohan V 

[Phil_Seamark · Accepted solution]

Hi @Anonymous

Please try this

Previous Value = 
VAR pre = MAXX(FILTER(ALL(Sheet2),[Time]<EARLIER([Time])),[Time])
RETURN 
MINX(FILTER(Sheet2,Sheet2[Time]=pre && 'Sheet2'[Key] = EARLIER('Sheet2'[Key])),[VALUE])

[Anonymous]

@Phil_Seamark That was quick and cool...

Thanks..:):)