Forum Discussion

AltGr9's avatar
AltGr9
Helper I
6 years ago
Solved

Cumulative Total Not Working Properly

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 e...
  • MartynRamsden's avatar
    MartynRamsden
    6 years ago

    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

MartynRamsden's avatar
MartynRamsden
Solution Sage
6 years ago

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's avatar
AltGr9
Helper I
6 years ago

Greatly appreciated!