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

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

Please try this expression as a measure (replacing Table name "Total" with 'data').

 

RunningTotal =
VAR thisdate =
SELECTEDVALUE ( Total[Date] )
RETURN
CALCULATE (
SUM ( Total[Amount] ),
ALL ( Total ),
VALUES ( Total[Account] ),
Total[Date] <= thisdate
)

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

mahoneypat ,

try like with a date table

Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(allselected(date),date[date] <=maxx(date,date[date])))
Cumm Sales = CALCULATE(SUM(Table[Amount]),filter(allselected(date),date[date] <=max(table[Date])))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.