Use filters or slicers to include several different time periods in a table

[Anonymous]

Hi,

I have a table with dates and several different columns. 
I would like to only include the first x dates, the middle x dates and the last x dates in that table.

Is it possible to add slicers or filters on the page to achieve this?
It needs to be user input friendly to select the range of the three different time periods.

Thanks.

[v-kelly-msft]

Hi @Anonymous ,

Do you wanna show as below?

If so,you only need to edit the interactions between the slicer and the table, if you dont want the table be filtered by the slicer,just click the slicer and and choose the interaction as below:

Best Regards,
Kelly

[Anonymous]

Looking for something like this:
Full table:

Date	Value
1/1/2020	14
1/2/2020	2
1/3/2020	12
1/4/2020	14
1/5/2020	17
1/6/2020	16
1/7/2020	2
1/8/2020	6
1/9/2020	9
1/10/2020	14
1/11/2020	17
1/12/2020	18
1/13/2020	20
1/14/2020	9
1/15/2020	3
1/16/2020	18
1/17/2020	13
1/18/2020	1
1/19/2020	12
1/20/2020	16
1/21/2020	11
1/22/2020	15
1/23/2020	13
1/24/2020	15
1/25/2020	5
1/26/2020	1
1/27/2020	20
1/28/2020	7
1/29/2020	12
1/30/2020	12

Then I would like the option to filter for several specific date intervals such as this output:

Date	Value
1/1/2020	14
1/2/2020	2
1/3/2020	12
1/10/2020	14
1/11/2020	17
1/12/2020	18
1/28/2020	7
1/29/2020	12
1/30/2020	12

[v-kelly-msft · Accepted solution]

Hi @Anonymous ，

I'm afraid it can't be realized just by filter or slicer as you wanna present 3 different periods of time,you need to create a new table instead,and the dax expression is as below:

Table 2 = 
VAR k =
    ADDCOLUMNS (
        'Table',
        "filter",
        VAR d1 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
        VAR d2 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 2 )
        VAR d3 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 3 )
        VAR d10 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 10 )
        VAR d11 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 11 )
        VAR d12 =
            DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 12 )
        VAR e1 =
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    'Table',
                    [Date].[Year] = EARLIER ( [Date].[Year] )
                        && [Date].[Month] = EARLIER ( [Date].[Month] )
                )
            )
        VAR e11 = e1 - 1
        VAR e12 = e1 - 2
        VAR e13 = e1 - 3
        VAR t = { d1, d2, d3, d10, d11, d12, e1, e12, e13 }
        RETURN
            IF ( [Date] IN t, 1, 0 )
    )
RETURN
    SELECTCOLUMNS (
        FILTER ( k, [filter] = 1 ),
        "Date", [Date],
        "City", [City],
        "Vist", [Visitor]
    )

Then you will see:

 
Best Regards,
Kelly

[Anonymous]

It's something similar to this I'm looking for, but I would like to have it all combined in one table, not three seperate tables.

[v-kelly-msft]

Hi @Anonymous ,

Can you show me your expected result?

Best Regards,
Kelly

[KHorseman]

I would start by creating a dedicated date table to offload the heavy lifting from your main data table. Create a relationship between the date column and a column of contiguous dates in the date table. Then create other columns as labels to work as filters. I don't know the specifics of the behavior you want but you could use a date diff column that labels each row with how many days ago it is, then have a number slicer to select a range of those for instance. Or if you want to have set blocks of dates (for instance if you want a button that always selects the last 30 days) you could have a column that labels all those dates with "Last 30 Days" and use that as a slicer.