Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Using one slicer on 2 different date columns

Hi,

 

I'm pulling data from only one table for my report. I have 2 date columns - effective date and expired date. I need to use only one slicer in my report so that I can apply filter on both effective date and expired date as below.

 

policy eff dt <= slicer date

and 

expired dt > slicer date

 

Please let me know if this can be achieved?

Thanks in advance.

Need Help
Tutorial Requests
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Anonymous ,

     

    1. Create a date table and add the date column in slicer.

     

    date = CALENDARAUTO()

    2. Create a measure and make the table visual filtered by the measure.

     

    Measure = 
VAR _exp =
    MAX ( Table1[expired date] )
VAR _eff =
    MAX ( Table1[effective date] )
VAR _sele =
    SELECTEDVALUE ( 'date'[Date] )
RETURN
    IF ( _eff <= _sele && _exp > _sele, 1, 0 )

     

    Please check the pbix as attached.

     

    Regards,

    Frank

1 Reply

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support
    7 years ago

    Hi Anonymous ,

     

    1. Create a date table and add the date column in slicer.

     

    date = CALENDARAUTO()

    2. Create a measure and make the table visual filtered by the measure.

     

    Measure = 
VAR _exp =
    MAX ( Table1[expired date] )
VAR _eff =
    MAX ( Table1[effective date] )
VAR _sele =
    SELECTEDVALUE ( 'date'[Date] )
RETURN
    IF ( _eff <= _sele && _exp > _sele, 1, 0 )

     

    Please check the pbix as attached.

     

    Regards,

    Frank