Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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])
Proud to be a Super User!
@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])
Proud to be a Super User!
@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 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |