Nested SUMIFS column in PowerBI

[Anonymous]

Hi, I have a table that looks like this:

Date	Code	Value	Adjusted value
01-01-2020	VAT	20	20
01-01-2020	Total short-term debt	-100	-120
01-02-2020	VAT	100	100
01-02-2020	Total short-term debt	-50	-150
01-03-2020	VAT	50	50
01-03-2020	Total short-term debt	-75	-125

I want to subtract the positive "VAT" rows from the "Total short-term debt" rows in an "Adjusted value" column whenever the month is the same, but only for "Total short-term debt" rows and not any other rows.

In Excel, I would do it like this: 

=IF(B2="Total short-term debt";C2-SUMIFS($C$1:$C$7;$A$1:$A$7;A2;$B$1:$B$7;"VAT");C2)

How can I do something similar in a custom column in PowerBI?

[ryan_mayu · Accepted solution]

@Anonymous 

Please try to create a column

Column = if('Table'[Code]="Total short-term debt",'Table'[Value]-sumx(FILTER('Table','Table'[Date]=EARLIER('Table'[Date])&&'Table'[Code]="VAT"),'Table'[Value]),'Table'[Value])

[Anonymous]

This one did the trick for my particular purpose. Thank you!

[Fowmy]

@Anonymous 

Add this as a custom column in your table in the model.

Adj Value = 
IF( Table4[Code] = "Total short-term debt",
    CALCULATE(
    SUMX(
        Table4,
        ABS(Table4[Value])
    ),
    ALLEXCEPT(Table4, Table4[Date])
    ),
Table4[Value]
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn