Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculate Previous Minute Values

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 L111:00:00 AM20
Input Current L111:03:00 AM34
Input Current L111:06:00 AM43
Input Current L111:09:00 AM58
Input Current L111:12:00 AM53
Input Voltage L111:00:00 AM230
Input Voltage L111:03:00 AM145
Input Voltage L111:06:00 AM170
Input Voltage L111:09:00 AM240
Input Voltage L111:12:00 AM280
Output Voltage L111:00:00 AM230
Output Voltage L111:03:00 AM233
Output Voltage L111:06:00 AM232
Output Voltage L111:09:00 AM231
Output Voltage L111:12:00 AM230
Output Current L111:00:00 AM23
Output Current L111:03:00 AM24
Output Current L111:06:00 AM25
Output Current L111:09:00 AM25
Output Current L111:12:00 AM26

 

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 

 

 

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    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])

2 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    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])