Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Filtering 2 date/time columns using a single date/time value

Hi,

 

I have multiple tables with the following scenario - 

(Date/times are in the format dd/mm/yyyy hh:mm:ss)

 

Row      Reference         Start Date/time              End Date/time

1           12345               15/01/2019 17:00:00      15/01/2019  17:20:15

2           12345                15/01/2019 17:20:15     25/02/2019  08:00:30

3           12345                25/02/2019 08:00:00     27/04/2019  16:03:00

 

These are different versions of the same instance, and as the item is changed we retain each version.  What we need to be able to do is filter the selection as at a particular point in time - for example, which version was active at 23:59:59 on 31/01/2019?  This should return only row 2 as the start date/time is before the report date/time and the end date/time is after the report date/time.  Row 1 was no longer active and row 3 was not yet active.  As can be seen from rows 1 & 2 there can be multiple versions on the same day, so we need to go down to the time of day as well as the date. 

 

I could use two filters, but that then allows the users the opportunity to put in 2 different date/times, which could lead to errors.  What I want to do is select a single date/time and apply it to two filters.

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    Thanks, but unless I'm missing something this doesn't return all of the rows that might meet the combined criteria.  I don't want to do any aggregation, I want to return a list of all of the rows that meet the criteria - there may be multiple items to be returned (I would normally expect to see several thousand).  So, adding some extra rows - 

     

    Row      Reference         Start Date/time              End Date/time                 Ref Desc     Another Col     And another

    1           12345               15/01/2018 17:00:00     15/01/2018 17:20:15        xxxxxx         yyyyyyyy          zzzzzzz

    2           12345               15/01/2018 17:20:15     25/02/2019 08:00:30        xxxxxx         yyyyyyyy          qqqqqq

    3           12345               25/02/2019 08:00:00     27/04/2019 16:03:00        xxxxxx         yyyyyyyy          vvvvvvv 

    4           67890               01/12/2018 14:00:00     15/01/2019 18:00:00        aaaaaa        bbbbbbb         ccccccc

    5           67890               15/01/2019 18:00:00     31/12/9999 23:59:59        aaaaaa        bbbbbbb         dddddd

    6           89784                01/01/2019 01:00:10    01/01/2019 01:03:10       abcdef        ghijklmn          opqrstuv

    7           89784                01/01/2019 01:03:10    01/05/2019 01:05:10        abcdef        ghijklmn          opqrstuv

    7           89784                01/05/2019 01:05:10    31/12/9999 23:59:59        abcdef        ghijklmn          opqrstuv

     

    If the report date is set to 01/01/2019 01:04:00, then I would expect rows 2, 4 and 7 to be returned.     

  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Anonymous 

    You may create a measure like below:

    Measure = 
IF (
    ISFILTERED ( Slicer[date/time] ),
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Start Date/time] <= SELECTEDVALUE ( Slicer[date/time] )
                && Table1[End Date/time] >= SELECTEDVALUE ( Slicer[date/time] )
        )
    ),
    BLANK ()
)

    Regards,

3 Replies

  • Mariusz's avatar
    Mariusz
    Community Champion
    7 years ago

    Hi Anonymous,

    Measure below should do the trick.

    Between Dates = 
VAR d = MAX('Calendar'[Date])
RETURN 
COUNTROWS(
    FILTER(
        CALCULATETABLE(
            yourTable,
            CROSSFILTER(yourTable[startDate], 'Calendar'[Date], None) --this deactivates relationship if there is one.
        ),
        yourTable[startDate] <= d 
        && yourTable[endDate] >= d
    )
)




    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Thanks, but unless I'm missing something this doesn't return all of the rows that might meet the combined criteria.  I don't want to do any aggregation, I want to return a list of all of the rows that meet the criteria - there may be multiple items to be returned (I would normally expect to see several thousand).  So, adding some extra rows - 

       

      Row      Reference         Start Date/time              End Date/time                 Ref Desc     Another Col     And another

      1           12345               15/01/2018 17:00:00     15/01/2018 17:20:15        xxxxxx         yyyyyyyy          zzzzzzz

      2           12345               15/01/2018 17:20:15     25/02/2019 08:00:30        xxxxxx         yyyyyyyy          qqqqqq

      3           12345               25/02/2019 08:00:00     27/04/2019 16:03:00        xxxxxx         yyyyyyyy          vvvvvvv 

      4           67890               01/12/2018 14:00:00     15/01/2019 18:00:00        aaaaaa        bbbbbbb         ccccccc

      5           67890               15/01/2019 18:00:00     31/12/9999 23:59:59        aaaaaa        bbbbbbb         dddddd

      6           89784                01/01/2019 01:00:10    01/01/2019 01:03:10       abcdef        ghijklmn          opqrstuv

      7           89784                01/01/2019 01:03:10    01/05/2019 01:05:10        abcdef        ghijklmn          opqrstuv

      7           89784                01/05/2019 01:05:10    31/12/9999 23:59:59        abcdef        ghijklmn          opqrstuv

       

      If the report date is set to 01/01/2019 01:04:00, then I would expect rows 2, 4 and 7 to be returned.     

      • v-cherch-msft's avatar
        v-cherch-msft
        Microsoft Employee
        7 years ago

        Hi Anonymous 

        You may create a measure like below:

        Measure = 
IF (
    ISFILTERED ( Slicer[date/time] ),
    CALCULATE (
        COUNTROWS ( Table1 ),
        FILTER (
            Table1,
            Table1[Start Date/time] <= SELECTEDVALUE ( Slicer[date/time] )
                && Table1[End Date/time] >= SELECTEDVALUE ( Slicer[date/time] )
        )
    ),
    BLANK ()
)

        Regards,