Forum Discussion

awal2018's avatar
awal2018
Regular Visitor
8 years ago

First Middle and Last Day

I am working for a hotel company, and we have a list of Guest ID's and their Stay dates, I want to determine which stays were a first, middle or last day.

ID Stay Date Frist Middle Last

21/1/2018First Day
21/2/2018MIddle Day
21/3/2018Last Day

\

 

 

Any tips?

2 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    Well, if is a MIN, then it is the First Day, MAX, last day, otherwise, Middle Day. So, some column like this:

     

    Column = 
VAR myMin = IF([Date] = CALCULATE(MIN([Date]),FILTER(ALL(aStays),[ID] = EARLIER(aStays[ID]))),1,0)
VAR myMax = IF([Date] = CALCULATE(MAX([Date]),FILTER(ALL(aStays),[ID] = EARLIER(aStays[ID]))),1,0)
RETURN IF(myMin,"First Day", IF(myMax,"Last Day", "Middle Day"))

     

     

    • awal2018's avatar
      awal2018
      Regular Visitor
      8 years ago

      I am getting some errors when the same guest stays with us later in the year.

       

      if the stay is from 1-1-1-3  1st day should be 1st, 2nd day should be middle and 3rd day should be last. but with that coulmn when the same guest stays with us again on 1-15 it marks the 3rd as a middle day and the 15th as a last day when it in fact should be a new first day. 

       

      Does that make sense?