Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi, Everyone!
I have a table of industrial measurements, that happens every minute. Therefore, i have a Column with name place, a column with Timestamp (date and time in minutes), and a column value. I'm trying to achieve the previous value (either with measure or calculated column), i.e, if the measurement refers with a same name place, it will retrieve the value immediately before it, for reasons of future comparison between the actual and previous value, in each row.
I've tried some resolutions found here, like this, but it did'nt work.
The core code I tried:
Solved! Go to Solution.
Hi @GuiCarvalho ,
I hope I understood your requirement. Typically I highly prefer using measures but in this case I tried it with a calcualted column.
So the following formula identifies in the first place the last relevant row for Nome de Origiem (var_EarlierEntryOfNomeDeOrigem). Based on the time of the last relevant row it gives you back the related value (formula after result).
Value before for name =
var EarlierEntryOfNomeDeOrigem =
CALCULATE(
MAX(fminuto[Timestamp]),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem])
&& fminuto[Timestamp]<EARLIER(fminuto[Timestamp])
)
)
RETURN
CALCULATE(
SELECTEDVALUE(fminuto[Value],BLANK()),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem])
&& fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
)
)
Please be aware that if there is no or more than one value for the same time in one category then the formual gives back BLANK(). When there is one distinct previous value for the category (Nome de Origem) then it should work.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi,
Try this calculated column formula
=lookupvalue(data[value],data[date],calculate(Max(Data[Date]),filter(Data,Data[Place]=earlier(Data[Place])&&data[date]<earlier(data[Date])))data[place],data[place])
Hope this helps.
Hi @GuiCarvalho ,
I hope I understood your requirement. Typically I highly prefer using measures but in this case I tried it with a calcualted column.
So the following formula identifies in the first place the last relevant row for Nome de Origiem (var_EarlierEntryOfNomeDeOrigem). Based on the time of the last relevant row it gives you back the related value (formula after result).
Value before for name =
var EarlierEntryOfNomeDeOrigem =
CALCULATE(
MAX(fminuto[Timestamp]),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem])
&& fminuto[Timestamp]<EARLIER(fminuto[Timestamp])
)
)
RETURN
CALCULATE(
SELECTEDVALUE(fminuto[Value],BLANK()),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=EARLIER(fminuto[Nome de Origem])
&& fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
)
)
Please be aware that if there is no or more than one value for the same time in one category then the formual gives back BLANK(). When there is one distinct previous value for the category (Nome de Origem) then it should work.
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
Hi @GuiCarvalho ,
I also managed to get the result when using a measure.
Measure Version Previous Row of Category =
var EarlierEntryOfNomeDeOrigem =
CALCULATE(
MAX(fminuto[Timestamp]),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=SELECTEDVALUE(fminuto[Nome de Origem])
&& fminuto[Timestamp]<MAX(fminuto[Timestamp])
)
)
RETURN
CALCULATE(
SELECTEDVALUE(fminuto[Value],"no value or multiple"),
FILTER(
ALL(fminuto),
fminuto[Nome de Origem]=SELECTEDVALUE(fminuto[Nome de Origem])
&& fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
@Mikelytics I'm trying to use your measure, but now i have to quantify when the previous value is equal to the actual one, and I tried to use IF 1 or 0, like the code below. Although, It returned only 1's for me. Could you check it out, please?
Hi @Mikelytics !
Thank you for your answer and agility!
Obviously, you code is correct, once it worked for you. Unfortunetly for me, it's retrieving "out of memory" error in both ways, calculated column (explicit error) and measure (loads forever). I've tried some tips found in this forum, like editing some settings and stuff, but no success.
I'll put your answer as the correct one, but if you know how to solve this, I'll be very grateful!
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |