Calculate Running balance for each month

[yafli]

I have a fixed budget for each budget line. I also have transction list for the whole period. I need to sum all transactions for each budget line and calculate the balance on monthly basis from the allocated budget for each line using DAX.

for example Budget line #1 has budget of 5000 USD, in Jan the expenses were 300, so I need Jan balance to appear as 4700. in Feb the expenses were 500, so I need the balance to appear as 4200.

[yafli]

Thanks,  This is working like a charm. Highly appreciated. 

[yafli]

Thanks a lot this is good,

But what if I have multiple items and each item has a fixed budget?

in your example, you put 5000 - var _1.  how can I do the calculation for all items.

item 1 budget is 5000

item 2 budget is 3000

item 3 budget is 2000.

I need to track the balance every month for each item. for example, the balance for item 1 in Jan is 500, in Feb it is 200, and in Mar it -100.

thanks again for your support.

[Anonymous]

Hi @yafli ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Measure =
VAR _1 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[date] <= SELECTEDVALUE ( 'Table'[date] )
                && 'Table'[item] = SELECTEDVALUE ( 'Table'[item] )
        ),
        'Table'[expense]
    )
RETURN
    5000 - _1

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yafli]

Here is a sample of the file where I have two tables.

Table 1 for the fixed budget and table 2 for the transaction. I want to get a column to show the expenses and balances for each AID as I filter each month or year.

[Anonymous · Accepted solution]

Hi @yafli ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Measure = 
VAR _1 =
    SUMX (
        FILTER (
            ALL ( 'Table' ),
            'Table'[Index]<=SELECTEDVALUE('Table'[Index])
                && 'Table'[Aids ]=SELECTEDVALUE('Table'[Aids ])
        ),
        'Table'[budget]
    )
var _2=CALCULATE(MAX('Table (2)'[amount]),FILTER(ALL('Table (2)'),'Table (2)'[aids]=SELECTEDVALUE('Table'[Aids ])))
return
_2-_1

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.