HELP! If I have a zero in a column how do I call the previous figure

[PSRai]

HI All,

I am working on providing some energy data from a electric box that we have at a customer site. I'm showing the energy in a certain period by MAX energy (minus) -MIN energy. If the electric box for whatever reason is switched off and on again for a short period, the MIN energy will drop to ZERO and the MAX will provide the current rating

I need a formula to say if current reading is less than previous reading then add previous reading to current reading?

Hope this makes sense

[Anonymous]

You could possibly try going into query editor and replacing all 0 values with null and using fillup/fill down to copy value from pervious cell into current cell if its blank(null) 

Alternatively, if you could share a sample dataset, it would help a lot. 

Hope this helps answering your question

[PSRai]

Unfortunately that doesn't work for me as the starting point will be 0. below is possibly a better explanation of what Im looking for 

I currently have data where by its measuring usage of each electrical sockets, Energy by date time (seconds).

I need to create a new column as below. Cant be done in excel due to the amount of data/rows

Date/Time                             Socket ID               Energy                New Column

18/10/2019 - 13:01:01               1                          0.1                           0.1
18/10/2019 - 13:01:05               1                          0.3                           0.3
18/10/2019 - 13:01:07               1                          0.5                           0.5
18/10/2019 - 13:01:09               1                          0.6                           0.6
18/10/2019 - 13:01:11               1                          1.0                           1.0

18/10/2019 - 13:01:15               1                          0.0                           1.0
18/10/2019 - 13:01:18               1                          0.3                           1.3
18/10/2019 - 13:01:20               1                          0.5                           1.5
18/10/2019 - 13:01:22               1                          0.7                           1.7

As you can see the Energy column reading increases at each reading from 18/10/2019 - 13:01:01 however when the socket is switched off and on the reading then sets to 0.0. what I need to do is bridge the gap. I need to create a new column as above and say *IF the current reading is less then the MAX reading then ADD MAX reading to current reading*. So in this case 1.0 is the MAX reading and lets say 0.0 is current I need to add 1.0 to 0.0.

also their are multiple sockets so it would look more like this,

Date/Time                  Socket ID           Energy                    New Column

18/10/2019 - 13:01:01      1                    0.1                               0.1
18/10/2019 - 13:01:01      2                    4.3                               4.3
18/10/2019 - 13:01:01      3                    5.5                               5.5
18/10/2019 - 13:01:01      4                    1.6                               1.6
18/10/2019 - 13:01:01      5                    1.2                               1.2

18/10/2019 - 13:01:15      1                    0.0                              1.0
18/10/2019 - 13:01:15      2                    0.1                              4.4
18/10/2019 - 13:01:15      3                    0.2                              5.7
18/10/2019 - 13:01:15      4                    0.0                              1.6
18/10/2019 - 13:01:15      5                    0.3                              1.5

Hope this makes sense?

[Anonymous]

Hi @PSRai ,

Please try the following.

1 - Go into query mode and remove the "-" from "Date Time Socket" so that the column can be converted to date/time format. Then change the column to date/time datatype

= Table.ReplaceValue(#"Sorted Rows"," - "," ",Replacer.ReplaceText,{"Date/Time Socket"})

2 - Create a new column ( i called it rank). The rank should increment with each new reading. There will be duplicate values as there can be multiple "Socket ID".

Rank = CALCULATE(
    COUNTROWS('Table'),
    FILTER(
        ALLEXCEPT('Table', 'Table'[Socket ID]), 
        'Table'[Date Time Socket] <= EARLIER('Table'[Date Time Socket]))
    ) 

 

3 - Then create another column (i called it advance). This will calculate the difference between current and previous reading

Advance = 
var curr = 'Table'[Energy]
var previous = CALCULATE(
    min('Table'[Energy]), 
    FILTER('Table', 
        'Table'[Rank]+1  = EARLIER('Table'[Rank]) && 
        'Table'[Socket ID] = EARLIER('Table'[Socket ID]) 
    ))

return curr - previous

 4 - Last create a summation column which will give you the desired output 

Output = 
    CALCULATE(
        SUM('Table'[Advance]), 
        FILTER('Table', 
            'Table'[Rank] <= EARLIER('Table'[Rank]) && 
            'Table'[Advance] >= 0 && 
            'Table'[Socket ID] = EARLIER('Table'[Socket ID])
        ))

Below is the final out 

Let me know if this solves your issue.

[PSRai]

Hi,

Im putting in the following and getting a error

Advance =
var curr = 'readings'[energy]
var previous = CALCULATE(
min('readings'[energy]),
FILTER('readings',
'readings'[Rank]+1 = EARLIER('readings'[Rank]) &&
'readings'[socket_id] = EARLIER('readings'[socket_id])
))

The syntax for ')' is incorrect. (DAX(var curr = 'readings'[energy]var previous = CALCULATE( min('readings'[energy]), FILTER('readings', 'readings'[Rank]+1 = EARLIER('readings'[Rank]) && 'readings'[socket_id] = EARLIER('readings'[socket_id]) )))).

[PSRai]

Thanks for your reply,

The table you have provided is nearly what i'm looking for however the below is the correct outcome. 

In this instance 1.00 is MAX Energy before Energy is 0, so what I need is 1.00 to be applied to all readings thereafter, so 1.00 

+ 0.00 (Output 1.00), 1.00+0.30 = (Output 1.30), 1.00+0.50 (Output 1.50) and so forth 

[Anonymous]

Hi @PSRai 

I think the outcome column is doing that. If not, could you copy my output column and outline what the "correct" output should besides it ?

[PSRai]

Hi please see below

So Energy Column MAX = 1.00 at 13:01:11, all readings after this date time has 1.00 applied to it 

MAX reading will vary per socket iD

Date Time Socket             Socket ID             Rank      Energy                 Advance                     Output

18/10/2019 13:01:01       1                              1              0.10                       0.10                       0.10

19/10/2019 13:01:05       1                              2              0.30                       0.20                       0.30

20/10/2019 13:01:07       1                              3              0.50                       0.20                       0.50

21/10/2019 13:01:09       1                              4              0.60                       0.10                       0.60

22/10/2019 13:01:11       1                              5              1.00                       0.40                       1.00

23/10/2019 13:01:15       1                              6              0.00                      -1.00                      1.00

24/10/2019 13:01:18       1                              7              0.30                        0.30                      1.30

25/10/2019 13:01:20       1                              8              0.50                        0.20                      1.50

26/10/2019 13:01:22       1                              9              0.70                        0.20                      1.70

27/10/2019 13:01:25       1                              10            0.00                        0.70                      1.00

28/10/2019 13:01:26       1                              11            0.20                        0.20                      1.20

[PSRai]

unfortunately its not letting me add the rank column as its saying not enough memory 

[PSRai]

for Rank

There's not enough memory to complete this operation. Please try again later when there may be more memory available.

[PSRai]

Sorry, needed to add if the current reading is less than the previous reading then add the previous reading to current

Date Time                                       MAX energy                      MIN Energy

18/10/2019 - 14:00:10                         10.1                                    3.52 

18/10/2019 - 14:00:11                         11.4                                    3.63

18/10/2019 - 14:00:12                         12.2                                    4.01

18/10/2019 - 14:00:13                         12.5                                    4.08

18/10/2019 - 14:00:14                          7.8                                    1.20 

MIN energy data 18/10/2019 - 14:00:14 is lower than previous data 18/10/2019 - 14:00:13, I need the current data to be added to the previous data?

The drop in data is due to the board being switched off for many reasons