Forum Discussion

setis's avatar
setis
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

EARLIER assistance

Dear experts, 

 

In the table below and travel bookings and every trip has a distinct pnrlocatorkey. I am trying to create a calculated column that calculates for every pnrlocatorkey the number of days between the Departure date when Travel_legs.firstleg = 1 and the Arrival date when Travel_legs.lastleg = 1. I'm stuck and I'd appreciate some assistance. Thanks 🙂

 

 

 

 

Need Help
  • setis's avatar
    setis
    6 years ago

    Dear jdbuchanan71 thanks for your answer. 

     

    I think that I got what I wanted using this:

     

    Trip_duration = 
CALCULATE(DATEDIFF(
    CALCULATE(
        MAX('public travelbooking'[Departure date]),'public travelbooking'[Travel_legs.firstleg] = 1),
        CALCULATE(
        MAX('public travelbooking'[Departure date]),'public travelbooking'[Travel_legs.lastleg] = 1),DAY),
        FILTER('public travelbooking','public travelbooking'[pnrlocatorkey] = EARLIER('public travelbooking'[pnrlocatorkey])))
  • jdbuchanan71's avatar
    jdbuchanan71
    6 years ago

    setis 

    Sorry, I was missing a piece in my measure, I think this will work for you.

    Trip_Duration =
DATEDIFF (
    CALCULATE (
        MAX ( 'public travelbooking'[Departure Date] ),
        'public travelbooking'[Travel_legs.firstleg] = 1,
        ALLEXCEPT ( 'public travelbooking', 'public travelbooking'[pnrlocatorkey] )
    ),
    CALCULATE (
        MAX ( 'public travelbooking'[Departure Date] ),
        'public travelbooking'[Travel_legs.lastleg] = 1,
        ALLEXCEPT ( 'public travelbooking', 'public travelbooking'[pnrlocatorkey] )
    ),
    DAY
)

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    setis 

    EARLIER relies on a row context which the measure does not have.  Instead try replacing your FILTER line with this.

    ALLEXCEPT ( 'public travelbooking'[pnrlocatorkey])

     

    • setis's avatar
      setis
      Icon for Post Partisan rankPost Partisan
      6 years ago

      Dear jdbuchanan71 thanks for your answer. 

       

      I think that I got what I wanted using this:

       

      Trip_duration = 
CALCULATE(DATEDIFF(
    CALCULATE(
        MAX('public travelbooking'[Departure date]),'public travelbooking'[Travel_legs.firstleg] = 1),
        CALCULATE(
        MAX('public travelbooking'[Departure date]),'public travelbooking'[Travel_legs.lastleg] = 1),DAY),
        FILTER('public travelbooking','public travelbooking'[pnrlocatorkey] = EARLIER('public travelbooking'[pnrlocatorkey])))
      • jdbuchanan71's avatar
        jdbuchanan71
        Icon for Super User rankSuper User
        6 years ago

        setis 

        Sorry, I was missing a piece in my measure, I think this will work for you.

        Trip_Duration =
DATEDIFF (
    CALCULATE (
        MAX ( 'public travelbooking'[Departure Date] ),
        'public travelbooking'[Travel_legs.firstleg] = 1,
        ALLEXCEPT ( 'public travelbooking', 'public travelbooking'[pnrlocatorkey] )
    ),
    CALCULATE (
        MAX ( 'public travelbooking'[Departure Date] ),
        'public travelbooking'[Travel_legs.lastleg] = 1,
        ALLEXCEPT ( 'public travelbooking', 'public travelbooking'[pnrlocatorkey] )
    ),
    DAY
)