topic Re: Incorrect Running Total based on Measure in DAX Commands and Tips

Incorrect Running Total based on Measure

Anonymous — Sat, 21 Nov 2020 11:56:27 GMT

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!

Re: Incorrect Running Total based on Measure

Fowmy — Sat, 21 Nov 2020 12:08:20 GMT

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

Re: Incorrect Running Total based on Measure

Anonymous — Sat, 21 Nov 2020 12:38:05 GMT

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:

Re: Incorrect Running Total based on Measure

CNENFRNL — Sat, 21 Nov 2020 12:38:02 GMT

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

Re: Incorrect Running Total based on Measure

Anonymous — Sat, 21 Nov 2020 12:52:09 GMT

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

Re: Incorrect Running Total based on Measure

Anonymous — Mon, 23 Nov 2020 08:14:51 GMT

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.