Skip to main content
cancel
Showing results for 
Search instead 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

Reply
JDanielHarvey
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.  
1 REPLY 1
v-yuta-msft
Community Support
Community Support

@JDanielHarvey ,

 


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

Helpful resources

Announcements
Europe Fabric Conference

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.

AugPowerBI_Carousel

Power BI Monthly Update - August 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

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

Sept NL Carousel

Fabric Community Update - September 2024

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