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 2 1/1/2...
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?