Forum Discussion
DAX with For and while loop
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
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-_culmativeHere'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.
3 Replies
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-solution-8e3eccb41bdaYou 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
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 youHi 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-_culmativeHere'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.
