Subtract the value of the column to the left from the higher value of a column

[Erick24]

Hello, I hope you can help me with the following:

1) When the row number in the table consumption is 1 must substract the quantity of the ID of the table quantity less consumption.

2) When the row number is different to 1 must substrat the higher value of the column project less the value of the column consumption like is in the image below:

Example of my Power Bi.

Consumption table

Quantity table:

Example in Excel:

[Anonymous · Accepted solution]

Hi @Erick24 ,

You can create a calculated column as below to get it, please find the details in the attachment.

Column =
VAR _qty =
    CALCULATE (
        MAX ( 'Quantity'[Quantity] ),
        FILTER ( 'Quantity', 'Quantity'[ID] = 'Consumption'[ID] )
    )
VAR _accsum =
    CALCULATE (
        SUM ( 'Consumption'[CONSUMPTION] ),
        FILTER (
            'Consumption',
            'Consumption'[ID] = EARLIER ( 'Consumption'[ID] )
                && 'Consumption'[ROW NUM] <= EARLIER ( 'Consumption'[ROW NUM] )
        )
    )
RETURN
    _qty - _accsum

Best Regards

[Erick24]

This is exactly what I was looking for. Thank you very much. 😊