Cumulative Total Not Working Properly

[AltGr9]

Hello Power BI Community!

I have a Fact table connected to a Month Dimension table using MonthID, and a running total showing Committed spend:

This shows me exactly what I want to see. I can see that the business was committed to 11,430.00 of spend until June (201906). Then in August they committed to an additional 15,870.60 spend, and in November (201911) they spent half of that 7,935.30. Great.

The problem is, I'm using the MonthID in the drill-down, and I don't want to because it looks ugly. I want it to look like the full month name, like this:

The problem, as you can see, is that now  my cumulative total for Committed has disappeared! And yet all I've done is change the field from 'Dimension Month'[MonthID]  to 'Dimension Month'[YearMonth]. 

My cumulative total for Committed is arrived at by subtracting my Received Running Total from my Ordered Running Total (as follows) - can anyone help me achieve a Committed running total with a full month name, as in the image above?

[MartynRamsden]

Hi @AltGr9 

Is the 'Month Name Alternative' column in your month dimension table sorted by another column?

If so, you'll need to include that in your measure too. Something like:

Ordered Running Total = 
VAR MaxMonthID = MAX ( 'Dimension Month'[Month ID] )
VAR Result =
CALCULATE (
	SUM ( 'Fact Monthly Project Rollup'[Capital Expenditure] ),
	ALL( 'Dimension Month'[Month Name Alternative], 'Dimension Month'[Month Name Alternative Sort]  ),
	'Dimension Month'[Month ID] <= MaxMonthID,
	'Dimension Capital Expenditure Type'[Capital Expenditure Type] = "Ordered"
)
RETURN Result

Best regards,

Martyn

[AltGr9]

Thank you very much Martyn. The column was indeed sorted by another, and I added that to the ALL clause of both of the measures making up my Committed measure: [Ordered Running Total] and [Received Running Total].

Unfortunately, it didn't work. Same result as before.

[MartynRamsden · Accepted solution]

Hi @AltGr9 

I think there must be another sort column in play (possibly on 'Dimension Month[YearMonth]).

Try the expression below, which will remove all filters from the 'Dimension Month' table apart from Month ID <= MaxMonthID:

Ordered Running Total =
VAR MaxMonthID = MAX ( 'Dimension Month'[Month ID] )
VAR Result =
    CALCULATE (
        SUM ( 'Fact Monthly Project Rollup'[Capital Expenditure] ),
        FILTER (
            ALL ( 'Dimension Month' ),
            'Dimension Month'[Month ID] <= MaxMonthID
        ),
        'Dimension Capital Expenditure Type'[Capital Expenditure Type] = "Ordered"
    )
RETURN
    Result

Best regards,

Martyn

[AltGr9]

Greatly appreciated!