Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Measure to subtract row value from most recent value

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

 

Need Help
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    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,

3 Replies

  • v-cherch-msft's avatar
    v-cherch-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        v-cherch-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        7 years ago

        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,