Forum Discussion
Incorrect Total
Justivan,
Here's a link to my updated pbix.
For this calculation only, it sounds like you need to filter out all the OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations except for the earliest In Date. I added a T/F calculated column to your Reservations table to note whether it is the earliest In Date for that combination, then included that flag in your Pax measure.
The numbers do change a bit, but the visual totals the way you would expect now that the cross-year bookings issue has been resolved. (I only validated the change in the 2025 number, so you may want to validate more thoroughly.)
Hopefully this is the solution you were looking for. 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.
Hi Wilson,
Sorry for the late reply as I was in a holiday. You are right and that's going to be a problem. Our business requires that it only consider it once based on the first day of the 'In Date'. So for example, with rows like this, pax should be counted only in 30/12/2023. Any idea how this should be done?
| RefID | ResID | HotelKey | OperatorKey | Booking Ref | Lead Guest | In Date | Out Date | Room Type | Days | Meal | Adult | Child |
| 188477 | 148022 | 10001072 | 3 | LVWBP | Lead Guest95490 | 30/12/2023 | 09/01/2024 | Superior Room - Ocean View | 10 | BB | 2 | 0 |
| 188895 | 148022 | 10001071 | 3 | LVWBP | Lead Guest95490 | 05/01/2024 | 09/01/2024 | Superior Room | 4 | BB | 2 | 0 |
Justivan,
Here's a link to my updated pbix.
For this calculation only, it sounds like you need to filter out all the OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations except for the earliest In Date. I added a T/F calculated column to your Reservations table to note whether it is the earliest In Date for that combination, then included that flag in your Pax measure.
The numbers do change a bit, but the visual totals the way you would expect now that the cross-year bookings issue has been resolved. (I only validated the change in the 2025 number, so you may want to validate more thoroughly.)
Hopefully this is the solution you were looking for. 🙂
----------------------------------
If this post helps, please consider accepting it as the solution to help other members find it quickly. Also, don't forget to hit that thumbs up and subscribe! (Oh, uh, wrong platform?)
P.S. Need a more in-depth consultation for your Power BI data modeling or DAX issues? Feel free to hire me on Upwork or DM me directly on here! I would love to clear up your Power BI headaches.