cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## FILTER(ALL(Dates) with External Filter Context

Hoping to get some clarity about how the ALL() (aka removefilter) function behaves inside FILTER() when using a measure in a chart that should be removing external filter context be it a pivot table or a chart.
I found this formula used in a video from Sam McKay in this video where he attempts to describe what is happening as: "The max date is always going to equal to the actual date. That's why we can create this running total.". His definition of actual date show's his cursor pointing to external/evaluation context from a table in a canvas report. My understanding is the ALL() function should be removing the external filter context but that doesn't seem to be the case with the formula below used in the video I provided a link to:

30 Day Running Total =
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[Date] > MAX( Dates[Date] ) - 30 &&
Dates[Date] <= MAX( Dates[Date] ) ) )

Worth noting, I have reviewed Alberto Ferrari's article on the ALL() function here. I also ran the formula through DAX Studio where the results provide 30 rows of every column in the date table from the last date in the report (12/29/2017) Sam McKay uses in the video.
Community Support

30 Day Running Total =
CALCULATE( [Total Sales],
FILTER( ALL( Dates ),
Dates[Date] > MAX( Dates[Date] ) - 30 &&
Dates[Date] <= MAX( Dates[Date] ) ) )

The expression seems to be correct, could you share some sample data give the expression of [Total Sales] and also share the expected result?

Regards,

Jimmy Tao

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors