Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GuiCarvalho
Frequent Visitor

Previous value with criteria

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:

CALCULATE (
    SUM(fminuto[Vazão]),
    FILTER (
        ALL ( fminuto ),
        fminuto[Timestamp]
            = CALCULATE (
                MAX ( fminuto[Timestamp] ),
                FILTER ( ALL ( fminuto ), fminuto[Timestamp] < SELECTEDVALUE( ( fminuto[Timestamp] ) )
            )
    )
))

Tried it e a lot of variations, but no success. Could Anyone help on this, please?
name column = "Nome de Origem"
DateTime with minutes column = "Timestamp"
Value column = "Vazão"

There is a sample of the table columns mentioned. Millions of rows.
GuiCarvalho_0-1667592774512.png

 

1 ACCEPTED SOLUTION
Mikelytics
Resident Rockstar
Resident Rockstar

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

Mikelytics_0-1667595377352.png

 

 

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

View solution in original post

6 REPLIES 6
Ashish_Mathur
Super User
Super User

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Mikelytics
Resident Rockstar
Resident Rockstar

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

Mikelytics_0-1667595377352.png

 

 

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

Hi @GuiCarvalho ,

 

I also managed to get the result when using a measure.

Mikelytics_2-1667595600659.png

 

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.

 

------------------------------------------------------------------
Visit my blog datenhungrig which I recently started with content about business intelligence and Power BI in German and English or follow me on LinkedIn!

@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?

GuiCarvalho_0-1668042640931.png

Congelamento =
VAR EarlierEntryOfNomeDeOrigem =
    CALCULATE(
        MAX(fminuto[Timestamp]),
        FILTER(
            ALL(fminuto),
            fminuto[Nome da Origem]=SELECTEDVALUE(fminuto[Nome da Origem])
            && fminuto[Timestamp]<MAX(fminuto[Timestamp])
        )
    )

VAR EarlierEntryOfVazao = CALCULATE(
    SELECTEDVALUE(fminuto[Vazão],"no value"),
    FILTER(
        ALL(fminuto),
        fminuto[Nome da Origem]=SELECTEDVALUE(fminuto[Nome da Origem])
        && fminuto[Timestamp] = EarlierEntryOfNomeDeOrigem
    )
)

VAR Congela = IF(EarlierEntryOfVazao=SELECTEDVALUE(fminuto[Vazão],"no value"), 1, 0)

RETURN
Congela

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!

 

GuiCarvalho_1-1667686841904.png

 

 

mangaus1111
Solution Sage
Solution Sage

Hi @GuiCarvalho ,

is this your expected result?

mangaus1111_0-1667594812425.png

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.