Incorrect Running Total based on Measure

[Anonymous]

I have a measure that calculates a value daily, and I'm trying to get a running total of that value.

My baseline measure is in the middle column; that's the value I'd like a running total for.

Here's the DAX for my running total column:

AUM DifferenceDailyDiscountedCFs $ RT =

VAR MaxDate = MAX(DailyAssets[as_of_date])

RETURN

CALCULATE(

[AUM DifferenceDailyDiscountedCFs $],

DailyAssets[as_of_date] <= MaxDate,

ALL(DailyAssets[as_of_date]))

In the image above, I'd expect the RT column to return -$286,940.50, followed by -$923,368.19 (derived from -$286,940.50 + -$636,427.69).

Please help.. Thanks!

[CNENFRNL]

@Anonymous , why not attach your pbix file for a quick troubleshooting; such a running total issue isn't supposed to be that complicated.

[Anonymous]

Unfortunately, I'm not able to attach the file for security reasons.

[Anonymous]

@Anonymous 

If you can't attach it for security reasons.... then create a file with meaningless data that will re-create the issue and then attach it. Simple.

[Fowmy]

@Anonymous 

Have you got a date table in your model and created a correct relationship??

In your formula, you have assigned the [as of date] column but the screenshot shows you have used the [Date] column. Either use the correct date column or modify your formula:

AUM DifferenceDailyDiscountedCFs $ RT = 
VAR MaxDate =  MAX(DailyAssets[as_of_date])
RETURN
CALCULATE(
    [AUM DifferenceDailyDiscountedCFs $],
    DailyAssets[as_of_date] <= MaxDate,
    ALL(DailyAssets[as_of_date])
)

If you are using the date table then,

AUM DifferenceDailyDiscountedCFs $ RT = 
VAR MaxDate =  MAX(Dates[Date])
RETURN
CALCULATE(
    [AUM DifferenceDailyDiscountedCFs $],
    Dates[Date] <= MaxDate,
    ALL(Dates[Date])
)

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

Good catch, but still returning incorrect result unfortunately. Updated measure:

AUM DifferenceDailyDiscountedCFs $ RT =

VAR MaxDate = MAX('Calendar'[Date])

RETURN

CALCULATE(

[AUM DifferenceDailyDiscountedCFs $],

'Calendar'[Date] <= MaxDate,

ALL('Calendar'[Date]))

Output: