How to substract values which are in different rows in Power BI?

[Analitika]

I wanted to substract CRE-DET exactly 8000-6000  but getting very strange wrong answers. 

Here I am using formula

New Measure =

VAR a = CALCULATE(SUM('x'[cre]),FILTER(ALL('x'),'x'[date]<MAX('x'[date])))+0

RETURN SUM('xj'[deb])-a

[v-rzhou-msft · Accepted solution]

Hi @Analitika 

You need to calculate CRE and DET separately. Add more conditions to the filter code of the calculation. Here I add DTE = 0 when calcualte CRE and add CRE = 0 when calcualte DET.

Substract = 
VAR _CRE =
    CALCULATE (
        SUM ( x[CRE] ),
        FILTER (
            ALL ( x ),
            x[ID] = MAX ( x[ID] )
                && x[OBJ_ID] = MAX ( x[OBJ_ID] )
                && x[DET] = 0
        )
    )
VAR _DET =
    CALCULATE (
        SUM ( x[DET] ),
        FILTER (
            ALL ( x ),
            x[ID] = MAX ( x[ID] )
                && x[OBJ_ID] = MAX ( x[OBJ_ID] )
                && x[CRE] = 0
        )
    )
RETURN
    _CRE - _DET

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.