Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Dynamic filtering on a date range with another date

Hi everyone, 

 

I have something to do in a report but I'm not sure it's possible.

I have a slicer on a date which is a 'between' slicer. The date for this one coming from a calendar table (linked to my contract table).

I have another slicer with another date, which is a list slicer. The date is a snapchot date.

What I would like is when I select a date 2 in the list, the range for the date 1 is automatically set to ==> First day of the month, Last day of the month : 

Does anyone has already try to do that?

 

Thank you in advance.

Need Help
  • v-janeyg-msft's avatar
    v-janeyg-msft
    4 years ago

    Hi, Anonymous 

     

    If there are other visual that need to be filtered according to this time period, it's hard to do. I need more datails.

    Only according to your description, You can create a distinct date table. then use it as slicer instead of calendar table.

    Table1 =DISTINCT ( Date[date] )

    Create a measure then use it in slicer filter pane.

    Like this:

    measure =
IF (
    Table1[date] <= EOMONTH ( SELECTEDVALUE ( mainTable[date] ), 0 )
        && Table1[date]
            >= EOMONTH ( SELECTEDVALUE ( mainTable[date] ), -1 ) + 1,
    1,
    0
)

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey

1 Reply

  • v-janeyg-msft's avatar
    v-janeyg-msft
    Community Support
    4 years ago

    Hi, Anonymous 

     

    If there are other visual that need to be filtered according to this time period, it's hard to do. I need more datails.

    Only according to your description, You can create a distinct date table. then use it as slicer instead of calendar table.

    Table1 =DISTINCT ( Date[date] )

    Create a measure then use it in slicer filter pane.

    Like this:

    measure =
IF (
    Table1[date] <= EOMONTH ( SELECTEDVALUE ( mainTable[date] ), 0 )
        && Table1[date]
            >= EOMONTH ( SELECTEDVALUE ( mainTable[date] ), -1 ) + 1,
    1,
    0
)

     

    Did I answer your question ? Please mark my reply as solution. Thank you very much.
    If not, please upload some insensitive data samples and expected output.


    Best Regards,

    Community Support Team _ Janey