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

Frequent Visitor

## FILTER with ALL still returns nothing

Hello helpers out there,

I'm trying to come up with a measure to calculate outside the date filter, ie, sum up the transaction cost in this case that took place after the last date in my date filter. For example, when I select 2022-Q2, the enddate will be '6/30/2022'. I have a visual that correctly calculates the total cost during Q2, and I need another measure (named 'Amt after') to calculate the total cost starting '7/1/2022' all the way through the last date in my record.  How do I get around by creating a new table that ignores the date selected in the filter? My statement below returns blank. I think "Transfer[shipdt] > [enddate]" messes up the conditions, so I attempted to use ALL to ignore the date filter, hoping filtertable will capture records with date > enddate. That did not work either. I'm stuck 😞 I would really appreciate if someone would help me out and point me to the right direction. Many thanks!!

Amt after =
VAR enddate = MAX(Transfer[shipdt])
VAR filtertable = FILTER(ALL(Transfer), Transfer[shipdt] <> BLANK() && Transfer[qtyship] > 0 && Transfer[shipdt] > [enddate])
VAR result = SUMX(filtertable, Transfer[linecost])
RETURN result
1 ACCEPTED SOLUTION
Community Support

You may try the following measure. If it doesn't work, can you tell me in which visual did you add the measure? Is there any other column in the same visual at the same time? Is your Quarter field in the slicer from the same 'Transfer' table? It would be helpful if you can provide some sample data and expected result in table format.

``````Amt after =
VAR v_enddate =
EOMONTH ( MAX ( Transfer[shipdt] ), 0 )
VAR filtertable =
FILTER (
ALL ( Transfer ),
Transfer[shipdt] <> BLANK ()
&& Transfer[qtyship] > 0
&& Transfer[shipdt] > v_enddate
)
VAR result =
SUMX ( filtertable, Transfer[linecost] )
RETURN
result
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

1 REPLY 1
Community Support

You may try the following measure. If it doesn't work, can you tell me in which visual did you add the measure? Is there any other column in the same visual at the same time? Is your Quarter field in the slicer from the same 'Transfer' table? It would be helpful if you can provide some sample data and expected result in table format.

``````Amt after =
VAR v_enddate =
EOMONTH ( MAX ( Transfer[shipdt] ), 0 )
VAR filtertable =
FILTER (
ALL ( Transfer ),
Transfer[shipdt] <> BLANK ()
&& Transfer[qtyship] > 0
&& Transfer[shipdt] > v_enddate
)
VAR result =
SUMX ( filtertable, Transfer[linecost] )
RETURN
result
``````

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

## Helpful resources

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.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

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

Top Solution Authors
Top Kudoed Authors