cancel
Showing results for
Did you mean:
Frequent Visitor

## FILTER(ALL on multiple

Hi,

I have a simple table with Date, Product Name and Value. I need a measure that will provide a Total 'value' for the dates <=  a date chosen by the user, of which the following works fine:

Measure = CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALL('0 - Measureables'),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))

HOWEVER, this completely ignores the 'Product Name' slicer (i assume because its referencng ALL. How can i adapt this so that the product name slicer is still filtered. Therefore giving a total for All previous dates for the chosen product name too.

Many Thanks

Stu

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator

## Re: FILTER(ALL on multiple

Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.

So we can create a measure using the formula as below:

`ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))`

After that we can get the result that we need.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
New Contributor

## Re: FILTER(ALL on multiple

If you simply want to display the total value for a specific set of dates, cant you use a date slicer together with a "measure = sum(measureables[value])?

Highlighted
Frequent Visitor

## Re: FILTER(ALL on multiple

Hi, thank you for the response. Unfortunately though i need to display sum of the values (total) for each measure but only until the date that the user selected, not all the dates in the table as some may be after this point. In the seperate column i have a measure showing exactly what you have suggested, but that only gives the total for the chosen day.

Hope this makes sense,

Thanks

Moderator

## Re: FILTER(ALL on multiple

Base on my test, we can use the function ALLEXCEPT instead of ALL here to make the product name keeping filtered.

So we can create a measure using the formula as below:

`ALLEX := CALCULATE(Sum('0 - Measureables'[Value]),(FILTER(ALLEXCEPT('0 - Measureables','0 - Measureables'[Product]),'0 - Measureables'[Date]<=(MAX('0 - Measureables'[Date])))))`

After that we can get the result that we need.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: FILTER(ALL on multiple

Brilliant Thank you.

Announcements

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 307 members 3,270 guests
Recent signins: