Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Interaction between two date slicers

I have two date slicers in Power BI. The first slicer has buttons with specific date periods like MTD, PM, YTD and Custom Date Range. The values of the date periods are created manually in a table with distinct IDs assigned to each date period, for eg. MTD - 1, PM - 2 etc. 

There is a second date slicer where the date value is controllered as a slider to change the date according to user's choice. The slider is only activated when the "Custom Date Range" button is selected in the first slicer.

The issue is when I select the "Custom Date Range" in the first slicer, then I go and change the slider to any value which doesn't include any specific time period, for eg. starting and ending date in the slider is '1/1/2023' and '4/30/2024', the slider doesn't reset to dafault values once I select any specific time period like PM or MTD, and the chart gives no records. See below screenshots:

 

 

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous ,

     

    I think this issue should be caused by the relationship between your Date table and your Fact Table.

    The filter on date will still keep impacting your visual when you select other methods like YTD/MTD...

    Here I suggest you to remove or inactive the filter between Date table and Fact Table.

    Then create a measure to filter your visual.

    MEASURE =
VAR _SELECTION =
    SELECTEDVALUE ( 'Slicer 1'[Order] )
VAR _OPENDATE =
    MAX ( 'Table'[Date] )
VAR _Custom =
    IF ( _OPENDATE IN VALUES ( 'Calendar'[Date] ), 1, 0 )
VAR _MTD =
    IF ( _OPENDATE >= EOMONTH ( TODAY (), -1 ) + 1 && _OPENDATE <= TODAY (), 1, 0 )
VAR _PM =
    IF (
        _OPENDATE
            >= EOMONTH ( TODAY (), -2 ) + 1
            && _OPENDATE <= EOMONTH ( TODAY (), -1 ),
        1,
        0
    )
VAR _YTD =
    IF ( YEAR ( _OPENDATE ) = YEAR ( TODAY () ), 1, 0 )
VAR _PY =
    IF ( YEAR ( _OPENDATE ) = YEAR ( TODAY () ) - 1, 1, 0 )
RETURN
    SWITCH ( _SELECTION, 1, _Custom, 2, _MTD, 3, _PM, 4, _YTD, 5, _PY, 1 )

    Add this measure into visual level filter and set it to show items when value = 1.

    Custom Date Range:

    MTD:

     

    Best Regards,
    Rico Zhou

     

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

     

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Anonymous ,

     

    I think this issue should be caused by the relationship between your Date table and your Fact Table.

    The filter on date will still keep impacting your visual when you select other methods like YTD/MTD...

    Here I suggest you to remove or inactive the filter between Date table and Fact Table.

    Then create a measure to filter your visual.

    MEASURE =
VAR _SELECTION =
    SELECTEDVALUE ( 'Slicer 1'[Order] )
VAR _OPENDATE =
    MAX ( 'Table'[Date] )
VAR _Custom =
    IF ( _OPENDATE IN VALUES ( 'Calendar'[Date] ), 1, 0 )
VAR _MTD =
    IF ( _OPENDATE >= EOMONTH ( TODAY (), -1 ) + 1 && _OPENDATE <= TODAY (), 1, 0 )
VAR _PM =
    IF (
        _OPENDATE
            >= EOMONTH ( TODAY (), -2 ) + 1
            && _OPENDATE <= EOMONTH ( TODAY (), -1 ),
        1,
        0
    )
VAR _YTD =
    IF ( YEAR ( _OPENDATE ) = YEAR ( TODAY () ), 1, 0 )
VAR _PY =
    IF ( YEAR ( _OPENDATE ) = YEAR ( TODAY () ) - 1, 1, 0 )
RETURN
    SWITCH ( _SELECTION, 1, _Custom, 2, _MTD, 3, _PM, 4, _YTD, 5, _PY, 1 )

    Add this measure into visual level filter and set it to show items when value = 1.

    Custom Date Range:

    MTD:

     

    Best Regards,
    Rico Zhou

     

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

     

  • saud968's avatar
    saud968
    Memorable Member
    1 year ago

    Here are a few approaches to resolve this issue:

    1. Dynamic Measure with DAX:

    Create a DAX measure that dynamically adjusts the date filter based on the selected period in the Period Slicer.
    Utilize the SELECTEDVALUE function to determine the current period selection.
    Employ the DATESBETWEEN function to define the date range based on the period.

    Example DAX Measure:

    Dynamic Date Filter =
    VAR SelectedPeriod = SELECTEDVALUE(PeriodTable[PeriodID])
    RETURN
    IF (
    SelectedPeriod = 1, // MTD
    CALCULATE(
    [YourMeasure],
    DATESBETWEEN(
    'YourDateTable'[Date],
    STARTOFMONTH(TODAY()),
    TODAY()
    )
    ),
    IF (
    SelectedPeriod = 2, // PM
    CALCULATE(
    [YourMeasure],
    DATESBETWEEN(
    'YourDateTable'[Date],
    DATEADD(TODAY(), -1, MONTH),
    LASTDAYOFMONTH(DATEADD(TODAY(), -1, MONTH))
    )
    ),
    // ... other periods ...
    CALCULATE(
    [YourMeasure],
    ALLSELECTED('YourDateTable'[Date]) // Default to all dates
    )
    )
    )

    2. Power BI's Time Intelligence Functions:

    Leverage functions like DATEADD, DATESINPERIOD, and STARTOFMONTH to dynamically calculate date ranges based on the selected period.
    Combine these functions with the SELECTEDVALUE function to determine the appropriate date range.

    3. Power BI's Relative Date Filtering:

    Utilize relative date filtering to directly set the date range based on the selected period.
    This can be done in the visual level filters or measure level filters.

    Best Regards
    Saud Ansari
    If this post helps, please Accept it as a Solution to help other members find it. I appreciate your Kudos!