Date Slicer

[chris05]

Hi,

I wanted to create a slicer similar to the below picture. My problem is it wont work as expected, for example, whenever I click MTD, Y and T wont show in the table display, its filtering based on the column values. and same for the other values.

My goal is if I click MTD it will show the data for month to date inluding today and Yesterday. same goes for YTD etc...

SelectedPeriod =
SWITCH(
SELECTEDVALUE(SlicerOptions[Period]),
"T", TODAY(),
"Y", TODAY() - 1,
"MTD", IF('Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()), 1) && 'Calendar'[Date] <= TODAY(), 1, 0),
"LM", IF('Calendar'[Date] >= DATE(YEAR(TODAY()), MONTH(TODAY()) - 1, 1) && 'Calendar'[Date] <= EOMONTH(TODAY() - 1, 0), 1, 0)
)

[Anonymous · Accepted solution]

Hi, @chris05 

Your idea is fantastic, but I would suggest checking the following:

1.Firstly, you need to ensure that you are using a measure rather than a calculated column or calculated table, as these cannot capture the values from slicers.

2.Secondly, here is my sample data:

3.I have modified your measure as follows:

SelectedPeriod =
VAR cc =
    SWITCH (
        SELECTEDVALUE ( SlicerOptions[Period] ),
        "T", IF ( MAX ( 'Calendar'[Date] ) = TODAY (), 1 ),
        "Y",
            IF ( MAX ( 'Calendar'[Date] ) = TODAY () - 1, 1 ),
        "MTD",
            IF (
                MAX ( 'Calendar'[Date] ) >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
                    && MAX ( 'Calendar'[Date] ) <= TODAY (),
                1
            ),
        "LM",
            IF (
                MAX ( 'Calendar'[Date] )
                    >= DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, 1 )
                    && MAX ( 'Calendar'[Date] )
                        <= EOMONTH ( TODAY () - 1, 0 ),
                1
            ),
        BLANK ()
    )
RETURN
    IF ( ISFILTERED ( 'SlicerOptions'[Period] ), cc, 1 )

4.Apply the measure to a visualization and modify filter settings:

5.Below are my final results, which I hope meet your expectations:

Please find the attached pbix relevant to the case.

Best Regards,

Leroy Lu

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