How count difference between two numbers per different row in Power BI?

[Analitika]

  

Hello,

I have a data in table. I need to count difference between CRE and DET. I need to count CRE-DET (8000-6000=2000). How to do it in DAX formula? I am expecting the result like this:

I have tried these: 

New column =

var _max = maxx(filter(Table, [C4] <[C4]),[C4])

var _val = sumx(filter(Table,[C4] =_max), [C2]+[C3])

return

[C2+[C3] - _val

   Column = VAR _0 = MAXX(FILTER('Fact','Fact'[Column4]<EARLIER('Fact'[Column4])),'Fact'[Column3]) VAR _3 = _0-CALCULATE(MAX('Fact'[Column2])) RETURN _3 

Measure=var Credit=SUM('your_table'[CRE])

var Debit=SUM('you_table'[DET])

return Credit-Debit

Sum(Table[CRE]) -Sum(Table[DET])

or

calculate(Sum(Table[CRE]) -Sum(Table[DET]), allexcept(Table, Table[ID]) )

or

calculate(Sum(Table[CRE]) -Sum(Table[DET]), filter(allselected(Table), Table[ID] = max(Table[ID])) )

VAR a = CALCULATE(SUM(MyTable[DRE]),FILTER(ALL(MyTable),MyTable[Date]<MAX(MyTable[Date])))+0

RETURN SUM(MyTable[CRE])-a

None of these helped me.

[v-chenwuz-msft · Accepted solution]

Hi @Analitika 

You can try the following formula to create a new column.

Difference =
VAR _maxCRE =
    MAXX( FILTER( 'Table', [ID] = EARLIER( 'Table'[ID] ) ), [CRE] )
VAR _maxDET =
    MAXX( FILTER( 'Table', [ID] = EARLIER( 'Table'[ID] ) ), [DET] )
RETURN
    IF( [DET] = _maxDET, _maxCRE - _maxDET, BLANK() )

I have the following questions, can you explain them?.

    1 you need column or measure?

    2 the condition choose 8000 and 6000 to calculate, is it they are the max of current capacity?

    3 only display the result in one row or all the rows?

I put my pbix file in the attachment you can reference.

Best Regards

Community Support Team _ chenwu zhu

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