FILTER(ALL on multiple

[Sdg8481]

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

[v-yuezhe-msft · Accepted solution]

@Sdg8481,

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.

 

For more information, please refer to the pbix as attached.

Regards,
Lydia

[Sdg8481]

Brilliant Thank you.

[tex628]

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])?

[Sdg8481]

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