AngusFair
New member
- Joined
- Jul 21, 2016
- Messages
- 3
- Reaction score
- 0
- Points
- 0
- Excel Version(s)
- 2013/365
I'm not sure how to phrase this question, so if it's been answered already, please point me in the right direction.
I'm trying to build an analysis of expected arrivals in a hotel.
I have a data table where each stay record is one line. There are columns for arrival date, entered date, cancelled date, rooms required.
If a client cancels before the arrival date, I don't want it counted. But if they cancel on the day of arrival, or they do not show up and have their reservation cancelled at 2 am that night (technically cancelled the day after), then I want them counted, as on the day of arrival, this would have appeared to be a valid reservation.
I have a column of dates on the left side of the pivot, and I need some way to refer to these dates in a formula. I need to count all the arrivals in my data table where the arrival date equals the date on the row in the left column, but where the cancel date is either empty (they didn't cancel) or not before that date. I have a dim_date table that is related to the arrival date column, so I can count ALL reservations with the respective arrival date, but I can't figure out how to filter the cancel date column.
In normal Excel, I'd build a table where I'd do something like SUMIFS(masterSTAY[ROOMS],masterSTAY[S_ARRIVAL],$B2,masterSTAY[S-CANDATE],">="&$B2), where B2 is the date in question, but I can't figure out how to replicate this in PowerPivot.
I've tried something like =CALCULATE(SUM(masterSTAY[ROOMS],masterSTAY[S_CANDATE]>=DIM_DATE[datekey]), but it tells me that I can't reference that many columns in a filter argument.
Thank-you,
AF
I'm trying to build an analysis of expected arrivals in a hotel.
I have a data table where each stay record is one line. There are columns for arrival date, entered date, cancelled date, rooms required.
If a client cancels before the arrival date, I don't want it counted. But if they cancel on the day of arrival, or they do not show up and have their reservation cancelled at 2 am that night (technically cancelled the day after), then I want them counted, as on the day of arrival, this would have appeared to be a valid reservation.
I have a column of dates on the left side of the pivot, and I need some way to refer to these dates in a formula. I need to count all the arrivals in my data table where the arrival date equals the date on the row in the left column, but where the cancel date is either empty (they didn't cancel) or not before that date. I have a dim_date table that is related to the arrival date column, so I can count ALL reservations with the respective arrival date, but I can't figure out how to filter the cancel date column.
In normal Excel, I'd build a table where I'd do something like SUMIFS(masterSTAY[ROOMS],masterSTAY[S_ARRIVAL],$B2,masterSTAY[S-CANDATE],">="&$B2), where B2 is the date in question, but I can't figure out how to replicate this in PowerPivot.
I've tried something like =CALCULATE(SUM(masterSTAY[ROOMS],masterSTAY[S_CANDATE]>=DIM_DATE[datekey]), but it tells me that I can't reference that many columns in a filter argument.
Thank-you,
AF
