Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
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
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?
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.
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]) )))).
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
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 ?
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
unfortunately its not letting me add the rank column as its saying not enough memory
for Rank
There's not enough memory to complete this operation. Please try again later when there may be more memory available.
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
User | Count |
---|---|
91 | |
73 | |
68 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
73 | |
60 | |
58 |