Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Nested SUMIFS column in PowerBI

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-202...
Need Help
  • ryan_mayu's avatar
    ryan_mayu
    6 years ago

    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])

Fowmy's avatar
Fowmy
Icon for Super User rankSuper User
6 years ago

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