Filter All help

[adamnimmo]

Howdy all, I am having some trouble attempting to calculate a sum outside of my slicer range. Currently I have a matrix visual with sales data listed and my slicer drills in monthly. I am trying to create an additional value column for my backlog (past due) orders, these are essentially a YTD sum based on whatever max date is selected within my slicer.

I have three measures setup

Backlog$ = SUM(Ex_Sales[Backlog]) 

Date_Selected = MAX(PB_DateKey[Date])

Backlog Sum = CALCULATE([Backlog$],FILTER(ALLEXCEPT(PB_DateKey,PB_DateKey[Date]),[Date_Selected] > MIN(Ex_Sales[Adj_Date])))

I am missing something as my results are not what expected. Thank you in advance for any help you can provide.

[GabrielSantos · Accepted solution]

Hey there! I was able to create a measure that delivers "a YTD sum based on whatever max date is selected within my slicer".

Using your defined [Backlog] measure, I wrote a measure as follows:

Calculate ( [Backlog],
                       DatesYTD( PB_DateKey[Date] )

Please keep in mind that this assumes a relationship between the Ex_Sales and PB_DateKey tables.

Please let me know if you have any questions!

Please find an example file here:

Dropbox link