Subtract the column from a fixed value

[Junaid11]

Hello,
I have a below table:

Date	Ependiture	Budget
1/1/2023	2354	9800
1/2/2023	34
1/3/2023	6786
1/4/2023	345
1/5/2023	57
1/6/2023	35
1/7/2023	34
1/8/2023	67
1/9/2023	57
1/10/2023	67

I want to subtract expenditure from budget based on date starting from ascendingly and want to see how much budget is remaining.
My outcome will look like below:

Date	Ependiture	Budget	Outcome
1/1/2023	2354	9800	7446
1/2/2023	34		7412
1/3/2023	6786		626
1/4/2023	345		281
1/5/2023	57		224
1/6/2023	35		189
1/7/2023	34		155
1/8/2023	67		88
1/9/2023	57		31
1/10/2023	67		-36

YOUR HELP WOULD BE HIGHLY APPRECIATED.
THANK YOU

[Anonymous · Accepted solution]

Hi @Junaid11 ,

Here is a measure that produces the precise table that you displayed in your question.

Outcome = 
VAR currentDate = 
SELECTEDVALUE('Table'[Date])
RETURN
    IF(
        HASONEVALUE('Table'[Date]),
        9800
           - CALCULATE(
                SUM('Table'[Ependiture]),
                'Table'[Date] <= currentDate,
                ALLEXCEPT('Table','Table'[Date])
            )
        ,9800 - sum('Table'[Ependiture])
    )

 The outcome is showed in the below picture.

There is a similar question which was answered by Chris Mendoza. Thanks for his contribution!
You can look it up through this link: Solved: Subtract the value by fixed value to get next subt... - Microsoft Fabric Community

Additional Tips:

In the future, if you have a various range of budgets, you may consider using the following step.

Go to Modeling ---- New Parameters ---- Numeric Range, and enter a range of numbers you would like to use as budget.

And then change the measure to a new version:

Outcome = 
VAR currentDate = 
SELECTEDVALUE('Table'[Date])
VAR budget =
SELECTEDVALUE('Table'[Budget])
RETURN
    IF(
        HASONEVALUE('Table'[Date]),
        [Parameter Value]
           - CALCULATE(
                SUM('Table'[Ependiture]),
                'Table'[Date] <= currentDate,
                ALLEXCEPT('Table','Table'[Date])
            )
        ,[Parameter Value] - sum('Table'[Ependiture])
    )

Now the table will change if the parameter value changes.

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]

@Junaid11 , with the help date table, create measures like

MTD Sales = CALCULATE(SUM(Table[Budget]) - SUM(Table[Ependiture]) ,DATESMTD('Date'[Date]))

Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA