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.
How do I create a measure calculating the difference in Money between Reveneu and Disbursement ONLY when Revenue > 0, i.e. when Revenue = 0 no subtraction should be made?
Customer | Money | Type | DateMonth |
1 | Revenue | 201701 | |
2 | 365 | Revenue | 201702 |
3 | Revenue | 201703 | |
3 | Revenue | 201704 | |
2 | 152 | Revenue | 201705 |
5 | 325 | Revenue | 201706 |
4 | Revenue | 201707 | |
1 | Disbursement | 201701 | |
2 | 18 | Disbursement | 201702 |
3 | 32 | Disbursement | 201703 |
3 | Disbursement | 201704 | |
2 | 54 | Disbursement | 201705 |
5 | Disbursement | 201706 | |
4 | 69 | Disbursement | 201707 |
Solved! Go to Solution.
Hi @David_1970,
If I understand you correctly, you should be able to firstly use the formula below to create a new calculate column in your table.
Diff = IF ( Table1[Type] = "Revenue" && Table1[Money] > 0, Table1[Money] - CALCULATE ( SUM ( Table1[Money] ), FILTER ( ALL ( Table1 ), Table1[Customer] = EARLIER ( Table1[Customer] ) && Table1[Type] = "Disbursement" && Table1[DateMonth] = EARLIER ( Table1[DateMonth] ) ) ) )
Then use the formula below to create a new measure to calculate the difference in Money between Revenue and Disbursement in your scenario.
Measure = SUM(Table1[Diff])
Note: You will need to replace Table1 with your real table name in the formulas above.
Regards
Hi @David_1970,
If I understand you correctly, you should be able to firstly use the formula below to create a new calculate column in your table.
Diff = IF ( Table1[Type] = "Revenue" && Table1[Money] > 0, Table1[Money] - CALCULATE ( SUM ( Table1[Money] ), FILTER ( ALL ( Table1 ), Table1[Customer] = EARLIER ( Table1[Customer] ) && Table1[Type] = "Disbursement" && Table1[DateMonth] = EARLIER ( Table1[DateMonth] ) ) ) )
Then use the formula below to create a new measure to calculate the difference in Money between Revenue and Disbursement in your scenario.
Measure = SUM(Table1[Diff])
Note: You will need to replace Table1 with your real table name in the formulas above.
Regards
Hi again,
By the way, what is the ALL-function all about - I mean, isn't that function supposed to eliminate any filters?
But since we are dealing with a calculated column (not a measure) there should be no filter context, or have I mixed things up?
Hi @David_1970,
You're right! The ALL function is not needed here. Thanks for pointing it out.
Regards
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |