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 Justivan,
I'll defer to you on whether it's legitimate or not in your data, but this must mean some OperatorKey, Booking Ref, Lead Guest, Adult, Child combinations appear in multiple years.
If you add Calendar Year to your _Reservation variable, the calculation works as you expect:
Pax =
VAR _Reservation =
SUMMARIZE (
FILTER ( 'Reservation', 'Reservation'[Status] <> "Can" ),
'Date'[Calendar Year],
'Reservation'[OperatorKey],
'Reservation'[Booking Ref],
'Reservation'[Lead Guest],
'Reservation'[Adult],
'Reservation'[Child]
)
VAR Result =
SUMX ( _Reservation, 'Reservation'[Adult] + 'Reservation'[Child] )
RETURN Result
----------------------------------
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 issue? 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.
