Tracking ongoing balance of a fund after deposits and withdrawls

[four2nian]

Hi everyone,

     I was hoping someone could help me track the ongoing balance in a company fund, where that company fund is regularly having money allocated into it, and having invoices take money out of the fund. More specifically, the fund will have money added to it, and will have deductions made from it, nearly monthly (sometimes multiple invoices to a fund in one month). I would like to have a time-series graph to show the balance remaining in the fund at the end of each month. But I would mainly like help with the formulas and DAX to calculate the balances, since once those are in place, a graph should be straight forward to make.

I would like essentially like to just calculate the differences between "Amount Allocated" and "Total Invoice Amount" on an ongoing-monthly basis.

I have 2 tables; one for the allocations and one for the invoices. Currently their relationship is based on the month. But I think I might need to change that. Their relationship is as follows:

Allocations          Invoices

Month *:* Month

Allocations:

Month	Fund Name	Amount Allocated
10/2021	Fund_A	$1,000,000
12/2021	Fund_B	$2,000,000
12/2021	Fund_C	$1,200,000
2/2022	Fund_A	$1,000,000

Invoices:

Month	Fund Name	Total Invoice Amount
11/2022	Fund_A	$250,000
1/2022	Fund_A	$150,000
2/2022	Fund_A	$100,000
1/2022	Fund_B	$100,000
1/2022	Fund_C	$100,000

Desired Result:

I would like the desired result to note that the Funds did not exists until the month in which they received thier first allocation. For instance, while Fund_A was created on 10/2021, Fund_B and Fund_C did not exist until they both recieved their first allocations until 2 months later.

Month	Fund Name	Balance
10/2021	Fund_A	$1,000,000
11/2021	Fund_A	$750,000
12/2021	Fund_A	$750,000
12/2021	Fund_B	$2,000,000
12/2021	Fund_C	$1,200,000
1/2022	Fund_A	$1,600,000
1/2022	Fund_B	$1,900,000
1/2022	Fund_C	$1,100,000
2/2022	Fund_A	$1,500,000
2/2022	Fund_B	$1,900,000
2/2022	Fund_C	$1,100,000

I apologize for formatting, as this is my first ever post. If you would like any clarification, I'm happy to provide more info to make sense of this problem. Thank you!

[gmsamborn · Accepted solution]

Hi @four2nian 

I would try to avoid that many-to-many relationship.

What I did was:
1) if needed, convert [Month] to a date in both ‘Allocations’ and ‘Invoices’
2) create a date table and relate it to [Month] in both fact tables
3) create a ‘DimFund’ table (in Power Query, Enter Data, or from a company source) and relate it to [Fund Name] in both fact tables. (I used Power Query in my example.)
4) [Balance] measure

Balance = 
VAR _Fund = MAX( 'DimFund'[Fund Name] )
VAR _Curr = MAX( 'Date'[Date] )
VAR _Alloc = 
    SUMX(
        FILTER(
            ALL( 'Allocations' ),
            'Allocations'[Month] <= _Curr
                && 'Allocations'[Fund Name] = _Fund
        ),
        'Allocations'[Amount Allocated]
    )
VAR _Invoice =
    SUMX(
        FILTER(
            ALL( 'Invoices' ),
            'Invoices'[Month] <= _Curr
                && 'Invoices'[Fund Name] = _Fund
        ),
        'Invoices'[Total Invoice Amount]
    )
VAR _Result = _Alloc - _Invoice
RETURN
    _Result

Let me know if this helps.

Running balance of accounts.pbix

[four2nian]

Thank you so much! This is exactly what I was looking for. I'm marking this as resolved. 

However, I did have an issue with how you made the table for unique fund names on the pbix file. Fortunately my list is very short, and I just manually created a table for mine. But do you mind explaining, or linking me to a resource that can describe how you created the "DimFund" table? I see that there's some power query code written that created "a_fund" and "i_fund" that used a couple functions like Binary.Decompress, and Json.document.  

I couldn't follow the code on how those 3 tables were created, and what the functions that created them were doing. Outside of that, I can't say thanks enough.

[gmsamborn]

Hi @four2nian 

The Binary.Decompress and Json.document is how data entered in "Enter Data" is represented in code.  You don't have to worry about that part at all.

What I did was make a copy of the "Allocations" table and remove any the columns other than [Fund_name].  Repeat this with "Invoices" and then append those 2 tables into a new tableand remove the duplicates.  (Mark those 2 temporary tables to not load.)

I'm glad that helped.