Forum Discussion

chris05's avatar
chris05
Frequent Visitor
1 year ago
Solved

Date Slicer

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

 






Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    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.

     

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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.