Forum Discussion

cpdanielmc21's avatar
cpdanielmc21
Icon for Helper I rankHelper I
6 years ago
Solved

Running Total solution for slicer

Hi community I've been trying to apply solutions proposed in this forum for similar issues to mine, but have not been successful so far.   I want to create either a measure or a column that can he...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Ok.  I played with this some more.  Please try this expression.  Having account in both the table and the slicer makes this a challenge. 

     
    RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))
    return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)
     

    Regards,

    Pat

cpdanielmc21's avatar
cpdanielmc21
Icon for Helper I rankHelper I
6 years ago

mahoneypat 

Thanks Pat, but still getting same result, 😞

I just returned the original formula for running total and created the new measure, but it gives same numbers

NewMeasure = SUMX(VALUES('Table'[Account]),[RunningTotal])
 
RunningTotal =
VAR thisdate =
SELECTEDVALUE ( 'Table'[Date])
RETURN
CALCULATE (
SUM ( 'Table'[Amount]),
ALL('Table'),
VALUES('Table'[Account]),
'Table'[Date]<= thisdate
)
mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

The reason you are seeing the weird #s is because the max date for Account 102 is less than the max for Account 101.  You need to get the max date in the selected month in the variable first with this type of change.

 

RunningTotalAll = var thisdate = CALCULATE(MAX(Total[Date]), ALL(Total), VALUES(Total[Month]))
return CALCULATE(SUM(Total[Amount]), ALL(Total), Total[Date]<= thisdate)
 
Regards,
Pat
 
  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    Ok.  I played with this some more.  Please try this expression.  Having account in both the table and the slicer makes this a challenge. 

     
    RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))
    return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)
     

    Regards,

    Pat

  • cpdanielmc21's avatar
    cpdanielmc21
    Icon for Helper I rankHelper I
    6 years ago

    mahoneypat 

    Hi Pat, thanks. I think is closer.

    It shows 24 for Month 1 and 62 for Month 2, (indeed correct), but not showing the "development" of 10, 20, then 24, then 39, etc up until 62.

     

    Also, if let's say I filter Account 102, should get 12, but get 62. In this case the original measure is the correct

     

  • cpdanielmc21's avatar
    cpdanielmc21
    Icon for Helper I rankHelper I
    6 years ago

    mahoneypat 

    Oh! This works!!

    I just took out the account column from the table, just kept it in the slider and now all numbers show as expected.

     

    Thank you so much Pat!

  • Tom2001's avatar
    Tom2001
    Regular Visitor
    3 years ago

    That is a great measure.

    RunningTotal = var thisdate = CALCULATE(MAX(Total[Date]), ALLSELECTED(Total[Account]))
    return CALCULATE(SUM(Total[Amount]), ALLEXCEPT(Total,Total[Account]), Total[Date] <= thisdate)

    But, I'm facing the situation when I'd need to have all accounts with balances even if they are not in the selection range "From ... To ...."
    So, it would be 0 for the Amount but some Running Total that came from all previouse/before the selected dates range periods.
    Could this measure be modified to do it?