Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Filtering Running Total

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 t...
Need Help
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    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

     

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , Try like


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

  • parry2k's avatar
    parry2k
    Super User
    6 years ago

    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!