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

     

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!