Forum Discussion

Sajeerahammed's avatar
Sajeerahammed
Regular Visitor
2 years ago
Solved

Time span

Dear All, 

I have particular issue. 

 

LocationStart TimeEnd Time
Start04-11-2023, 08:01:23 AM04-11-2023, 08:38:25 AM
Carrefour City Centre04-11-2023, 08:38:25 AM04-11-2023, 08:38:41 AM
Carrefour City Centre04-11-2023, 08:38:41 AM04-11-2023, 08:42:38 AM
Carrefour City Centre04-11-2023, 08:42:38 AM04-11-2023, 08:42:48 AM
Carrefour City Centre04-11-2023, 08:42:48 AM04-11-2023, 09:18:20 AM
MONOPRIX SHOP04-11-2023, 09:18:20 AM04-11-2023, 09:18:33 AM
MONOPRIX SHOP04-11-2023, 09:18:33 AM04-11-2023, 09:34:57 AM
carrefour,landmark04-11-2023, 09:34:57 AM04-11-2023, 10:43:29 AM
carrefour,landmark04-11-2023, 10:43:29 AM04-11-2023, 10:43:40 AM
carrefour,landmark04-11-2023, 10:43:40 AM04-11-2023, 10:44:21 AM

 

 

TimeRequired DAta
04-11-2023, 08:50:07 AMCarrefour City Centre
04-11-2023, 09:36:30 AMcarrefour,landmark

 

There are two Data table.I would like to pull the location from first table according to time in 2nd table.But the problem is that mentioned time in 2nd table is precise and it is in between start time and end time in first table..Please help..

Need Help
  • danextian's avatar
    danextian
    2 years ago

    Hi Sajeerahammed ,

     

    Try this calc column in  Table02:

    Location =
CALCULATE (
    MAX ( Table01[Location] ),
    FILTER (
        Table01,
        EARLIER ( Table02[Time] ) <= Table01[End Time]
            && EARLIER ( Table02[Time] ) >= Table01[Start Time]
    )
)

     

     

3 Replies

  • danextian's avatar
    danextian
    Super User
    2 years ago

    Hi Sajeerahammed ,

     

    Try this calc column in  Table02:

    Location =
CALCULATE (
    MAX ( Table01[Location] ),
    FILTER (
        Table01,
        EARLIER ( Table02[Time] ) <= Table01[End Time]
            && EARLIER ( Table02[Time] ) >= Table01[Start Time]
    )
)

     

     

    • Sajeerahammed's avatar
      Sajeerahammed
      Regular Visitor
      2 years ago

      Brother...

      Can we add salesman criteria also on desired output??thanks in advance for your support

      StartSalesman NameStart TimeEnd Time
      Start 04-11-2023, 08:01:23 AM04-11-2023, 08:38:25 AM
      Carrefour City CentreSuraj04-11-2023, 08:38:25 AM04-11-2023, 08:38:41 AM
      Carrefour City CentreSuraj04-11-2023, 08:38:41 AM04-11-2023, 08:42:38 AM
      Carrefour City CentreSuraj04-11-2023, 08:42:38 AM04-11-2023, 08:42:48 AM
      Carrefour City CentreSuraj04-11-2023, 08:42:48 AM04-11-2023, 09:18:20 AM
      MONOPRIX SHOPSuraj04-11-2023, 09:18:20 AM04-11-2023, 09:18:33 AM
      MONOPRIX SHOPSuraj04-11-2023, 09:18:33 AM04-11-2023, 09:34:57 AM
      carrefour,landmarkRaja04-11-2023, 09:34:57 AM04-11-2023, 10:43:29 AM
      carrefour,landmarkRaja04-11-2023, 10:43:29 AM04-11-2023, 10:43:40 AM
      carrefour,landmarkRaja04-11-2023, 10:43:40 AM04-11-2023, 10:44:21 AM

       

      TimeSalesmanDesired output
      04-11-2023, 08:50:07 AMSurajCarrefour City Centre
      04-11-2023, 09:36:30 AMRajacarrefour,landmark