Measure to subtract row value from most recent value

[Anonymous]

Hi all,

I have a table that contains the last 5 tvalues for each Prod_ID ordered by date desc.

I need help with a measure that will calculate the value at a row minus the most recent value for each Prod_ID.

End result should look like this;

Thanks in Advance

[v-cherch-msft]

Hi @Anonymous 

You may create a calculated column with below formula:

Column =
VAR Recent_date =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[tValue] ),
        FILTER ( 'Table', 'Table'[Date] = Recent_date )
    ) - 'Table'[tValue]

Regards,

[Anonymous]

Hi @v-cherch-msft ,

Thanks for your reply, it's great but something is lacking.

My table has more than 2000 rows with some Prod_ID having the same latest dates.

What the formula does is for those products, it will sum all their tValues whereas I need it to calculate for each Distinct Prod_ID.

Thanks

[v-cherch-msft · Accepted solution]

Hi @Anonymous 

You may try to create a measure like below:

Measure =
VAR Recent_date =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ) )
RETURN
    CALCULATE (
        SUM ( 'Table'[tValue] ),
        FILTER ( ALLEXCEPT ( 'Table', 'Table'[Prod_ID] ), 'Table'[Date] = Recent_date )
    )
        - CALCULATE ( SUM ( 'Table'[tValue] ) )

Regards,