DAX with For and while loop

[Akolade]

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

[v-stephen-msft · Accepted 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.           

[Akolade]

@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

[amitchandak]

@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