Join us at FabCon Atlanta from March 16 - 20, 2026, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.
Register now!Calling all Data Engineers! Fabric Data Engineer (Exam DP-700) live sessions are back! Starting October 16th. Sign up.
I have this table
Account Ref | Amount | Date | Org code |
1 | 25 | 24 February 2000 | A |
2 | 25 | 24 February 2000 | A |
3 | 25 | 14 March 2000 | B |
4 | 25 | 20 March 2000 | B |
5 | 34 | 4 April 2000 | B |
6 | 12 | 10 April 2000 | A |
7 | 30 | 24 may 2000 | A |
I have another table called pot Fund
Organisation | InitialFund |
B | £1000 |
A | £2000 |
Please I need to create a DAX that will filter each row. If the Org code = "A" then the amount will be deducted from pot fund "A".
For example for Account "1" the Amount is £25 and the org = "A" which will be £2000- £25 = £1975. The next column that will satisfied the condition, the calculation will be £1975 - [Amount]
Thank you
Solved! Go to Solution.
Hi @Akolade ,
You can create a calcualted column. And you need to create a relationship first.
Column = var _initial=RELATED('pot Fund'[InitialFund])
var _culmative=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Date]<=EARLIER('Table'[Date])&&[Org code]=EARLIER('Table'[Org code])&&[Account Ref]<=EARLIER('Table'[Account Ref])))
return _initial-_culmative
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Akolade ,
You can create a calcualted column. And you need to create a relationship first.
Column = var _initial=RELATED('pot Fund'[InitialFund])
var _culmative=CALCULATE(SUM('Table'[Amount]),FILTER('Table',[Date]<=EARLIER('Table'[Date])&&[Org code]=EARLIER('Table'[Org code])&&[Account Ref]<=EARLIER('Table'[Account Ref])))
return _initial-_culmative
Here's the result.
Best Regards,
Stephen Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@amitchandak for your time. What I really need is DAX that will Perform a sort of row context and iteration for the upper table with a condition
for example: If the Org code = "A" then the amount on that row which is 25 will be duducted from the £2000 in the pot fund table leaving the balance to £1975.
The next row which is Account 2 with the same organisation(A) the Amount (25) will be deduccted from the balance (£1975). The same goes with org code "B"
The idea is to know the balance of the pot fund at any point in time.
Thank you
@Akolade , Not very clear.
Create a common dimension or table org
and analyze with a measure
Sum(Table2[Initial Fund]) - Sum(Table1[Amount])
You can use a filter of date and company for Table 1
Power BI- DAX: When I asked you to create common tables: https://youtu.be/a2CrqCA9geM
https://medium.com/@amitchandak/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-soluti...
You might need cumulative for table 1 amount
Continue to explore Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
Join the Fabric FabCon Global Hackathon—running virtually through Nov 3. Open to all skill levels. $10,000 in prizes!
Check out the September 2025 Power BI update to learn about new features.