Forum Discussion

simonchung's avatar
simonchung
Frequent Visitor
4 years ago
Solved

Filter based on date range

Hi PBI Buddies,    I have a sales table simplifed as follow:  There is a date table as a slicer, so that when I select date 2021/09/01, record ID 1 will be displayed in a table visual selec...
Need Help
  • timg's avatar
    timg
    4 years ago

    Oke if I understand correctly you always select one date in the slicer, and then want to see all records in the table where that selected date is between the start and end date of that record? 

    In that case perhaps this will work for you:

    1. create a measure which return 1 if the selected date (SELECTEDVALUE()) is between the start and end date of the row

    2. place that measure in the filter pane and set it to "value is 1"

    3. now, when you select a date in your date slicer, the table will only return the rows where the measure calculation results in a 1.

     

    here is an example of the measure:

    SelectedDate =
IF (
    MIN ( Test[start] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( Test[end] ) >= SELECTEDVALUE ( 'Date'[Date] );
    1;
    0
)

    and a test where this method seems to work:

    Hope that solves it for you,

     

    Tim

timg's avatar
timg
Solution Sage
4 years ago

Hi Simon,

 

This should do the trick. Below you will find an example of the end result.

The CONCATENATEX ( VALUES ( TestTable[ID] ); TestTable[ID]; "," );  concats all your column values

The FILTER(.... filters the concatenated values to only include the id's that appear between the start and end date.

ConcatID =
CALCULATE (
    CONCATENATEX ( VALUES ( TestTable[ID] ); TestTable[ID]; "," );
    FILTER (
        'TestTable';
        TestTable[DateStart] < SELECTEDVALUE ( 'Calendar'[Date] )
            && TestTable[DateEnd] > SELECTEDVALUE ( 'Calendar'[Date] )
    )
)

 

 

Hope that helps!

Regards,

 

Tim

  • simonchung's avatar
    simonchung
    Frequent Visitor
    4 years ago

    Thanks so much, really appreciate your help, it's closer.
    However it is not my expected result. 
    In my case, there are 2 visuals, 1 is a slicer and 1 is a table
    The Date table is used as a Slicer, when I select "2021/09/07", the Table would show the details of ID 2 & 3 (Mello/400, Cello/900 etc...)
    When I select "2021/09/08" or "2021/09/09", the Table would show the details of ID 2 to 5 (Mello..Cello.. Gello.. Kello...)
    So on... 

    • timg's avatar
      timg
      Solution Sage
      4 years ago

      Oke if I understand correctly you always select one date in the slicer, and then want to see all records in the table where that selected date is between the start and end date of that record? 

      In that case perhaps this will work for you:

      1. create a measure which return 1 if the selected date (SELECTEDVALUE()) is between the start and end date of the row

      2. place that measure in the filter pane and set it to "value is 1"

      3. now, when you select a date in your date slicer, the table will only return the rows where the measure calculation results in a 1.

       

      here is an example of the measure:

      SelectedDate =
IF (
    MIN ( Test[start] ) <= SELECTEDVALUE ( 'Date'[Date] )
        && MAX ( Test[end] ) >= SELECTEDVALUE ( 'Date'[Date] );
    1;
    0
)

      and a test where this method seems to work:

      Hope that solves it for you,

       

      Tim

      • simonchung's avatar
        simonchung
        Frequent Visitor
        4 years ago

        😍😍😍It works perfectly, thank you so much!
        As I will receive files daily and accumulate them, but most of the data are repeated
        This action can help saving many file sizes.