FILTER with ALL still returns nothing

[danny_dmv]

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

[v-jingzhang · Accepted solution]

Hi @danny_dmv 

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.