Filtering Running Total

[Anonymous]

Hi there,

I'm facing some trouble here when trying to insert a filtering condition for a Running Total of cumulative sales.

Althought the running total without filter runs perfectly, when I try to put a filter into the sentence it stops showing a "growth" line and starts showing a line that goes up and down

My Running Total, one which is running perfectly is:

M Sales SUM running total over Date =
CALCULATE(
SUM('Sales'[Value]);
FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

My objective now is being able to run a DAX like this, and still showing in my chart a running total growing line:

M Filtered Sales SUM running total over Date =
CALCULATE(
SUM('Sales'[Value]);
FILTER('Sales';'Sales'[Country]="Brazil");
FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

Can you help me ?

[Icey]

Hi @Anonymous ,

Is this problem solved?

Best Regards,

Icey

[amitchandak]

@Anonymous , Try like

CALCULATE(
SUM('Sales'[Value]);
FILTER( ALLSELECTED('Sales'); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) && 'Sales'[Country]="Brazil"))

[parry2k]

@Anonymous As a best practice, add date dimension in your model and use it for and time intelligence calculations. There are many posts on how to add date dimension and below is the link to a few. Once the date dimension is added, mark it as a date table on table tools.

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
https://radacad.com/create-a-date-dimension-in-power-bi-in-4-steps-step-1-calendar-columns

Add following measure and it will work more efficient on large datasets

SUM('Sales'[Value]);
FILTER( ALLSELECTED('CalendarTable'[Date]); 'CalendarTable'[Date] <= MAX ( 'CalendarTable'[Date] ) ),
'Sales'[Country]="Brazil"
)

Let's try to follow the best practice for scalable solutions. I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

[mahoneypat · Accepted solution]

Try this measure instead.  Filtering the whole Sales table is affected your filters more than you intended.  Note that I changed your ALLSELECTED() part too.  If this doesn't work, you can change it back to ALLSELECTED('Sales').  I don't know your model, so could be either way.

M Filtered Sales SUM running total over Date =
CALCULATE(
SUM('Sales'[Value]);
'Sales'[Country]="Brazil";
FILTER( ALLSELECTED('Sales'[Date]); 'Sales'[Date] <= MAX ( 'Sales'[Date] ) ))

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

Regards,

Pat