Return Last Value for each Attribute by Another Column Value

[cbruhn42]

I have a table of measurements above.  I want to return the last measurement Value right before the Tank Conditioned column changes to Yes.  The highlighted values of 1008 for ASBC and 3.9 for EBC would be the correct result.  I'm not sure if this is best done in a measure or a calculated column and I can't figure out how to get this done.

[cbruhn42]

OK, I used a combination of solutions here to get it to work half the time.

This works for when there is a production order that goes from Tank Conditioned = No to Yes.  However, I can't figure out yet how to return the last values for brands that never go from No to Yes.

HEre is what I have so far.

Last Value =

VAR CurrentOrder = 'BBT Turbidity and Cell Counts'[JFProductionOrder]

VAR CurrentAttribute = 'BBT Turbidity and Cell Counts'[Attribute]

VAR Dt = 'BBT Turbidity and Cell Counts'[Turbidity DT]

RETURN

IF(

    'BBT Turbidity and Cell Counts'[Tank Conditioned] = "Yes",

        MAXX(FILTER('BBT Turbidity and Cell Counts',

            'BBT Turbidity and Cell Counts'[JFProductionOrder] = CurrentOrder &&

            'BBT Turbidity and Cell Counts'[Attribute] = CurrentAttribute &&

            'BBT Turbidity and Cell Counts'[Tank Conditioned] <> "Yes" &&

            'BBT Turbidity and Cell Counts'[Turbidity DT] < Dt

        ), [Value]),

IF(

    'BBT Turbidity and Cell Counts'[Tank Conditioned] = "No",

        MAXX(FILTER('BBT Turbidity and Cell Counts',

            'BBT Turbidity and Cell Counts'[JFProductionOrder] = CurrentOrder &&

            'BBT Turbidity and Cell Counts'[Attribute] = CurrentAttribute &&

            'BBT Turbidity and Cell Counts'[Tank Conditioned] <> "Yes" &&

            'BBT Turbidity and Cell Counts'[Turbidity DT] < Dt

        ), [Value]), blank()))

[v-cgao-msft]

Hi @cbruhn42 ,

Please crate a calculate column like:

Last Value = 
VAR CurrentOrder = 'Table'[JFProductionOrder]
VAR CurrentAttribute = 'Table'[Attribute]
VAR Dt = 'Table'[Turbidity DT]
RETURN
IF(
    'Table'[Tank Conditioned] = "No",
    CALCULATE(
        LASTNONBLANK('Table'[Value],1),
        FILTER(
            ALL('Table'),
            'Table'[JFProductionOrder] = CurrentOrder &&
            'Table'[Attribute] = CurrentAttribute &&
            'Table'[Turbidity DT] < Dt &&
            'Table'[Tank Conditioned] <> "Yes"
        )
    )
)

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[cbruhn42]

This doesn't quiet work.  If I only have one measurement set with before the tank is conditioned I get blank values.

If I have a brand that never switches to Tank Conditioned = Yes, then I get the incorrect values.

WE're close though! Thank you for the help.

[cbruhn42]

Forgot to add that I do have a date column.  It is the date/time the tank is conditioned.  Basically when the 'Tank Conditioned' column goes from No to Yes it uses this date column to determine that.

[amitchandak]

@cbruhn42 , I think we need to have a date or index column to find data less than this, but you can try like

New column = maxx(filter(Table, [Attribute] = earlier([Attribute])  && [Tank Condition] = "No") , [Value])

or

New column = if([Tank Condition] = ""Yes"  , maxx(filter(Table, [Attribute] = earlier([Attribute])  && [Tank Condition] =

"No") , [Value]) , blank())

refer

Power BI DAX- Earlier, I should have known Earlier: https://www.youtube.com/watch?v=cN8AO3_vmlY&t=17820s&list=PLPaNVDMhUXGYU97pdqwoaociLdwyDRn39&index=1

[cbruhn42]

@amitchandak, do you have another idea now that I submitted the additional information regarding date column?

[cbruhn42]

Definately need a date here I think to get this to work right.  Here is a snapshot of the table with the dates I have available.  The other piece I forgot to mention is that there are multiple production orders this occurs on in the data set.  So the values returned are specific to that production order that starts with MR.