Subtract the column from a fixed value

Question

Hello,I have a below table:DateEpenditureBudget1/1/2023235498001/2/202334&nbsp;1/3/20236786&nbsp;1/4/2023345&nbsp;1/5/202357&nbsp;1/6/202335&nbsp;1/7/202334&nbsp;1/8/202367&nbsp;1/9/202357&nbsp;1/10/202367&nbsp;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:DateEpenditureBudgetOutcome1/1/20232354980074461/2/202334&nbsp;74121/3/20236786&nbsp;6261/4/2023345&nbsp;2811/5/202357&nbsp;2241/6/202335&nbsp;1891/7/202334&nbsp;1551/8/202367&nbsp;881/9/202357&nbsp;311/10/202367&nbsp;-36YOUR HELP WOULD BE HIGHLY APPRECIATED.THANK YOU

anonymous · Accepted Answer

Hi&nbsp;Junaid11&nbsp;,
&nbsp;
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] &lt;= currentDate,
                ALLEXCEPT('Table','Table'[Date])
            )
        ,9800 - sum('Table'[Ependiture])
    )

&nbsp;
&nbsp;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
&nbsp;
Additional Tips:
In the future, if you have a various range of budgets, you may consider using the following step.
&nbsp;
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] &lt;= currentDate,
                ALLEXCEPT('Table','Table'[Date])
            )
        ,[Parameter Value] - sum('Table'[Ependiture])
    )

&nbsp;
&nbsp;
Now the table will change if the parameter value changes.
&nbsp;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
Best Regards,
Stephen Tao
&nbsp;
If this post&nbsp;helps, then please consider&nbsp;Accept it as the solution&nbsp;to help the other members find it more quickly. &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;

Forum Discussion

Subtract the column from a fixed value

2 Replies

Related content

Fixed Index Column

Subtraction calulation

Fixed column width

Subtract two columns

Subtract one month from date column

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