Table or measure to get a period filter

[314mp_M0th4]

Hello, I have a dataset with sales with dates connected to a calendar table. Let's say the dataset spans 5 years. I want to create a dropdown slicer with the following options : 

1. This month

2. Last month

3. This Year

4. Last Year

5. All 

And more if needed. Question is how one would implement this. Is this a measure or a calculated column? 

My first idea would be a calculated column with a switch statement for each category but I'm not sure how I would then include "This month" with "This year" and so on. 

Any ideas? - Thanks in advance.

[314mp_M0th4 · Accepted solution]

What I ended up doing was creating two calculated columns, one for month periods ad other for year periods, both with a switch function. Then I used two slicers for the calculated columns.

[Adescrit]

Hi @314mp_M0th4 

A calculated table would actually be your best option. To create such a table you can use DAX such as the below.

Timeframe Selection = 
VAR __CurrentYear = YEAR(TODAY())
VAR __LastYear = __CurrentYear - 1
VAR __ThisMonth = MONTH( TODAY() )
RETURN
    UNION(
        ADDCOLUMNS( 
            DATESMTD(dimDate[DATE] ),
            "Timeframe", "This Month",
            "Index", 1
        ),
        ADDCOLUMNS( 
            DATEADD( SUMMARIZECOLUMNS(dimDate[DATE], FILTER(dimDate ,dimDate[MONTH] = __ThisMonth && dimDate[YEAR] = __CurrentYear)), -1, MONTH ),
            "Timeframe", "Last Month" ,
            "Index", 2

        ),
        ADDCOLUMNS(
            DATESYTD( dimDate[DATE] ),
            "Timeframe", "This Year",
            "Index", 3
        ),
        ADDCOLUMNS( 
            SUMMARIZECOLUMNS(dimDate[DATE], FILTER(dimDate ,dimDate[YEAR] = __LastYear)),
            "Timeframe", "Last Year" ,
            "Index", 4

        )
    )

If you then create a relationship between this table (the date column) and your calendar dimension table, you can use the Timeframe column in a slicer to quickly filter. It will be a many-to-many relationship, but as long as you ensure your slicer is single-select it should work well.

FYI you may need to tweak the DAX code to fit your model and specific requirements but hopefully this is a guide.

The index column is optional but allows you to apply a custom sorting to how the timeframes appear in the slicer (as opposed to alphabetical order).