Running Total of a Measure

[PBrainNWH]

I'm trying to get a running total of my Variance column. Variance is a measure of (Budget - Spend). Here is the Running Total measure I've created and the results. The weird thing is it starts the running total in the first month where there's no spend amount (January), otherwise it's just giving me the total of the Variance column until then.

VarRunning Total =

    VAR maxdate = MAX('Calendar (2)'[Date])

    VAR varAmt = 'PL&PIL&Budget Append'[Budget Amount]-'PL&PIL&Budget Append'[Spend Amount]

    RETURN

    CALCULATE(

        SUMX('PL&PIL&Budget Append','PL&PIL&Budget Append'[Variance]),

        'Calendar (2)'[Date] <= maxdate,

        ALL('Calendar (2)'[Date])

    )

[CoreyP]

Think you want this:

Cumulative = 
VAR _maxdate = MAX( 'Date'[Date] )
VAR _logic =
SUMX( 
    FILTER( 
        ALL('Date') , 'Date'[Date] <= _maxdate ) , 
        [Variance] 
)

RETURN
_logic

[PBrainNWH]

Here's what I entered:

VarRunning Total =

VAR _maxdate = MAX( 'Calendar (2)'[Date] )

VAR _logic =

SUMX(

    FILTER(

        ALL('Calendar (2)'[Date]) , 'Calendar (2)'[Date] <= _maxdate ) ,

        [Variance]

)

RETURN

_logic

The results are the same:

[CoreyP]

I have a feeling it's a problem with your date periods since you are using custom fiscal periods. 

[CoreyP]

Can you please share a sample of your data and a screenshot of your model?

[PBrainNWH]

Here is a sample of data from the main table:

Here is the portion of the model:

Both the Budget and Spend Amounts are measures constructed as such:

Budget Amount = CALCULATE(SUM('PL&PIL&Budget Append'[Line Amount]),FILTER('PL&PIL&Budget Append','PL&PIL&Budget Append'[Status]="Budget"))+0

[CoreyP]

And the FY24 Month column in your visual is coming from the Calendar (2) table, yes?

[PBrainNWH]

Yes.

[CoreyP · Accepted solution]

I've tried replicating, and mine seems to work. Here is how mine is built, take a look and compare with yours for any "ah-ha's."

Sample Data:

Model:

Measures:

Budget = CALCULATE( SUM( 'P&L Sample'[LineAmount] ) , 'P&L Sample'[Status] = "Budget" )

Spend = CALCULATE( SUM( 'P&L Sample'[LineAmount] ) , 'P&L Sample'[Status] = "Spend" )

Variance_ = [Budget] - [Spend]

VAR Running Tot = 
VAR _maxdate = MAX( 'Date'[Date] )
VAR _logic =
SUMX( 
    FILTER( 
        ALL('Date') , 'Date'[Date] <= _maxdate ) , 
        [Variance_] 
)

RETURN
_logic

Output:

[PBrainNWH]

Still getting the same results. I'm perplexed. Closing thread for now. I'll take a look at a few things.