All values of a column that is not in the selected date range

[Anonymous]

Hello,

I have an issue that I want to get all the values of a column that is outside the selected date range. I have a single table with eomdate, amount, category, type. It has data from Jan 2015 to till date. Now if I filter the eomdate- Jan 2019 to Aug 2019 then I want all the values that are outside this selected date range, ie from Jan 2015 to Dec 2018 and Sep 2019 to Nov 2019

I will be using a visual that will be having amount, category, type. I want it to show the data based on the above explanation. Can anyone help me with this?

[v-xuding-msft]

Hi @Anonymous ,

I create a sample that you could reference.

• Please create a new calendar table as a slicer. And there is no relationship between the tables.

Calendar = CALENDARAUTO()

 

• Create a measure

Measure =
CALCULATE (
    SUM ( 'Table'[ Sales] ),
    FILTER (
        'Table',
        'Table'[Date] <= MIN ( 'Calendar'[Date] )
            || 'Table'[Date] >= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.

[Anonymous]

Hello @v-xuding-msft ,

Thanks for the response. I am not working with the number column. I am working with a text column. Can you please update the formula accordingly for me?

[v-xuding-msft · Accepted solution]

Hi @Anonymous ,

Please try to change SUM funtion to MAX.

Measure =
CALCULATE (
    MAX('Table'[Segment]),
    FILTER (
        'Table',
        'Table'[Date] <= MIN ( 'Calendar'[Date] )
            || 'Table'[Date] >= MAX ( 'Calendar'[Date] )
    )
)

Best Regards,

Xue Ding

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Kudos are nice too.