Accumulating Sum (Running Total) not taking into consideration my filters

[Anonymous]

hello everyone i want to create a running total/  accumulate sum of my measure, i tried

CALCULATE(SUM(Table[Amount]), dimDate[Date] <= MAX(dimDate[Date]))

it works but doesnt start from the start date i assigned instead goes back to the first date on my table and accumulates.

i have 4 date slicers, year, quarter, month, and week which i have to take into consideration

in addition, i want to see them in a matrix table which the column section has quarter and week number drilled down

thats how my page and matrix table looks like so i need to have an accumulated based on this

best regards

[Anonymous · Accepted solution]

Hi,@Anonymous .Hello,@Joe_Barry ,thanks for your concern about this issue.
And I would like to share some additional solutions below

According to your description, you want the summed value to take into account the slicer's choice, not the whole table.

This is the result of accumulating the whole table without adding a slicer

When I use the original code, after changing the filter range of the slicer, the calculation of the measure remains unchanged.

Using the ALLSELECTED function you can achieve your needs:

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

Here is the DAX code

M_result = 
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED(dimDate),dimDate[Date] <= MAX(dimDate[Date])))

M_result = 
CALCULATE(SUM('Table'[Amount]),FILTER(ALLSELECTED(dimDate),dimDate[Date] <= MAX(dimDate[Date])))

Measure = 
CALCULATE(SUM('Table'[Amount]),dimDate[Date] <= MAX(dimDate[Date]))

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.

Best Regards,

Carson Jian,

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Joe_Barry]

Hi @Anonymous 

On which column does your date table have a relationship with? The Measure will look at this column when calculating. So for example you have an active relationship with the Created date, then it will show the culamtive value on these dates. If you want to use a date column in your fact table that doesn't have a relationship with the date table, then create an inactive relationship between these columns and use USERELATIONSHIP to activate it. Have you marked your Date table as a Date table? Doing this will reduce the amount of Dax you need to write when doing time intelligence measures.

Also look a this video to create a Running Total Measure https://www.youtube.com/watch?v=fR0dI5UfZD8

Joe

[Anonymous]

hello, i dont think it matters i am using everything from the dimtable all slicers and columns are from dimtable and have many to one relationship between dimtable and other tables and my question is to have running total/ accumulated sum so w1 was 100$ week 2 was 300$ then the result i want to see is week 1 100$ week 2 400$(week 1+week2) and then week 3 should be (week1+ week2 + week3)...