Running total with criteria and using two measures

[MattJM]

Hello,

I have a problem with my report where I’m trying to create a measure that adds the cumulative actual of the most recent month by the budget of the following month. This value needs to be cumulative/running total. Here's a sample of what I'm currently seeing in my Power BI report that I made in Excel - 

Here's the two DAX I have for this - the issue in both comes in the blue text. As you can see, I'm trying to add two measures together, which has got me stuck.

Measure1 =

VAR running_act =

    CALCULATE ([YTDAct],

        FILTER(

            ALLEXCEPT(Overhead,Overhead[MonthDate],Overhead[Department]), (Overhead[IndexMonth] <= MAX(Overhead[IndexMonth]))))

    RETURN

    IF([YTDAct] = 0, CALCULATE([BudgSUM] + running_act,

        FILTER(ALL(Overhead),Overhead[IndexMonth] <= MAX( Overhead[IndexMonth]))),running_act)

(‘[BudgSUM]’ – is just a sum of the total where the total = “budget”
AND
‘[YTDAct]’ is the same – total money where the money = “Actual”)

AND

Measure2 =

VAR running_act =

    CALCULATE ([YTDAct],

        FILTER(

            ALLEXCEPT(Overhead,Overhead[Department]), (Overhead[IndexMonth] <= MAX(Overhead[IndexMonth]))))

RETURN

IF([YTDAct] = 0, [BudgSUM] + running_act, [CumulativeAct])

Any help is well appreciated 🙂

[amitchandak]

@MattJM , Use one way to get cumm budget and actual using date table connected to date of you tbale 

Cumm Actual = CALCULATE(SUM(Sales[Actual Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Actual = CALCULATE(SUM(Sales[Actual Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Cumm Budget = CALCULATE(SUM(Budget[Budget Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))

Cumm Budget = CALCULATE(SUM(Budget[Budget Amount]),filter(allselected(date),date[date] <=max(date[Date])))

Budget = SUM(Budget[Budget Amount])

Actual = SUM(Budget[Actual Amount])

Cumm Actual with Budget

Cumm = CALCULATE(Sumx(values('Date'[Month Year]),if(isblank([Actual]), [Actual], [Budget] ))  ,filter(allselected('Date'),'Date'[date] <=max('Date'[date])))

Running Total/ Cumulative:
https://www.youtube.com/watch?v=h2wsO332LUo&list=PLPaNVDMhUXGaaqV92SBD5X2hk3TMNlHhb&index=42

Power BI Window function Rolling, Cumulative/Running Total, WTD, MTD, QTD, YTD, FYTD: https://youtu.be/nxc_IWl-tTc
https://medium.com/@amitchandak/power-bi-window-function-3d98a5b0e07f

[MattJM]

Thanks for the reply,

This didn't work for me. I apoligize, I don't think I mentioned that every column in that Excel screen shot from my original post is a "measure" in Power BI (other than the date).

The orginal Excel source has one "Total" column, followed by a "Budget" or "Actual" column for each.
Current source:

Your Cumm Actual and Cumm Budget do not work since the Cumulative Actual and monthly budget are just measures - but that's okay because the current measures I have in place work. The issue is when adding them/Cumm measure.

I added a date table and messed around with the your Cumm measure. This is what I have and what output I'm getting.

Cumm = CALCULATE(Sumx(Values(DateTable[Date]),if([CumulativeAct] = 0, [CumulativeAct], [BudgSUM] )) ,filter(allselected('DateTable'),'DateTable'[Date] <=max('DateTable'[date])))

Output: 1,157,900 (the cumulative budget for July)

I'm trying to get the monthly budget to add to the actual cumulative. In the screenshot I posted, The 1,156,000 of the Cumulative Actual (N) needs to add to 2023 Budget (K) 157,00 of Aug.

Appreciate the help so far. I'll keep working on it in the meantime. Thanks!