Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Incorrect Running Total based on Measure

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!

5 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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:

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

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

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        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.