Cumulative Measure

[Ramenboi11]

I have a column of an original budget revenue which I want to sum cumulatively with a column of budget change orders for each month, I can't find any helpful Formulas.

I can't show the real data for privacy but I wrote a table below with fake numbers that is what my matrix looks like right now.

Month.     Org. budget.    Change Order 

                200000.             00

jan.           00.                     -1600

feb           00.                      +17000

mar          00.                     +13000

[techies · Accepted solution]

Hi @Ramenboi11 can you please check if this is as required

Cumulative Budget =

VAR SelectedDate = MAX('Date'[Date])

VAR InitialBudget =

    CALCULATE(

        MAX(BudgetData[OriginalBudget]),

        FILTER(

            ALL(BudgetData),

            NOT(ISBLANK(BudgetData[OriginalBudget]))

        )

    )

VAR TotalChange =

    CALCULATE(

        SUM(BudgetData[ChangeOrder]),

        FILTER(

            ALL('Date'),

            'Date'[Date] <= SelectedDate

        )

    )

RETURN

    InitialBudget + TotalChange

[rohit1991 · Accepted solution]

Hi @Ramenboi11 

To calculate the cumulative budget in Power BI, you need to start by identifying the original budget amount, which typically appears only once (often at the start or in a separate row).

Then, create a DAX measure that adds this original budget to the cumulative sum of monthly change orders. This can be done by using a measure that retrieves the maximum original budget from non-blank and non-zero entries, and then another measure that sums the change orders up to the current month.

To ensure proper ordering, especially if your month column is text (like "Jan", "Feb"), create a separate column that assigns a numerical value to each month and use it to sort the visual.

The final measure will calculate the cumulative budget by adding the initial budget to the sum of all change orders up to the selected month, allowing you to track how your budget evolves over time.

[v-aatheeque]

Hi @Ramenboi11 ,

If a community member’s response addressed your query, please mark it as Accepted Answer and click Yes if you found it helpful.

Should you have any further questions, feel free to reach out.
Thank you for being a part of the Microsoft Fabric Community Forum!

[v-aatheeque]

Hi @Ramenboi11 ,

we haven't heard back from you regarding our last response and wanted to check if your issue has been resolved.

 If the response addressed by the community member for  your query, please mark it as Accept Answer and click Yes if you found it helpful.

Thank You.

[v-aatheeque]

Hi @Ramenboi11 ,

If the information is helpful, please accept the answer by clicking the "Upvote" and "Accept Answer" on the post. If you are still facing any issue, please let us know in the comments. We are glad to help you.

We value your feedback, and it will help us to assist others who might have a similar query. Thank you for your contribution in enhancing Microsoft Fabric Community Forum.

[Ashish_Excel]

Hi,

I am sure that table should have atleast 3 columns - Date, budget amount and change amount.  Share a sample of the raw data table withthese 3 columns.

[techies · Accepted solution]

Hi @Ramenboi11 can you please check if this is as required

Cumulative Budget =

VAR SelectedDate = MAX('Date'[Date])

VAR InitialBudget =

    CALCULATE(

        MAX(BudgetData[OriginalBudget]),

        FILTER(

            ALL(BudgetData),

            NOT(ISBLANK(BudgetData[OriginalBudget]))

        )

    )

VAR TotalChange =

    CALCULATE(

        SUM(BudgetData[ChangeOrder]),

        FILTER(

            ALL('Date'),

            'Date'[Date] <= SelectedDate

        )

    )

RETURN

    InitialBudget + TotalChange

[pankajnamekar25]

Total Org Budget =

CALCULATE(

    SUM(BudgetData[OrgBudget]),

    FILTER(BudgetData, NOT(ISBLANK(BudgetData[OrgBudget])) && BudgetData[OrgBudget] <> 0)

)

Create the Cumulative Measure

Cumulative Budget =

VAR OrgBudget =

    CALCULATE(

        SUM(BudgetData[OrgBudget]),

        FILTER(BudgetData, NOT(ISBLANK(BudgetData[OrgBudget])) && BudgetData[OrgBudget] <> 0)

    )

VAR CumulativeChange =

    CALCULATE(

        SUM(BudgetData[ChangeOrder]),

        FILTER(

            ALLSELECTED(BudgetData),

            BudgetData[Month] <= MAX(BudgetData[Month])

        )

    )

RETURN

OrgBudget + CumulativeChange

Thanks,
 Pankaj Namekar | LinkedIn

If this solution helps, please accept it and give a kudos (Like), it would be greatly appreciated.

[Ramenboi11]

I tried This solution however the column just shows how much each change order was for what I wanted to see was

        Change.       Cumulative Budget

jan    0.                 2000

feb     +500.          2500

mar.   -750.          1750