Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
314mp_M0th4
Resolver I
Resolver I

Table or measure to get a period filter

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.

1 ACCEPTED SOLUTION
314mp_M0th4
Resolver I
Resolver I

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.

View solution in original post

2 REPLIES 2
314mp_M0th4
Resolver I
Resolver I

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
Impactful Individual
Impactful Individual

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).


Did I answer your question? Mark my post as a solution!
My LinkedIn

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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