Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have two tables with hotel reservations. The first is for future reservations and the other is for past ones from last year.
FutureReservations
arrival_date | departure_date | total_nights | rate_cat | rate | month_year | revenue | entered_on |
12/2/2022 | 12/5/2022 | 3 | IDMES | 125 | Dec-2022 | 375 | 11/20/2022 |
12/2/2022 | 12/3/2022 | 1 | IGKLM | 105 | Dec-2022 | 105 | 11/1/2022 |
12/4/2022 | 12/8/2022 | 4 | IMMLR | 101 | Dec-2022 | 404 | 11/1/2022 |
12/4/2022 | 12/5/2022 | 1 | IDTHO | 119 | Dec-2022 | 119 | 10/20/2022 |
12/6/2022 | 12/10/2022 | 4 | IMMLR | 105 | Dec-2022 | 420 | 11/20/2022 |
12/4/2022 | 12/8/2022 | 4 | IGKLM | 120 | Dec-2022 | 480 | 11/15/2022 |
12/3/2022 | 12/5/2022 | 2 | IDMES | 129 | Dec-2022 | 258 | 11/15/2022 |
12/30/2022 | 1/2/2023 | 3 | IMGOV | 100 | Dec-2022 | 300 | 11/22/2022 |
1/2/2023 | 1/10/2023 | 8 | IDMES | 119 | Jan-2023 | 952 | 11/23/2022 |
1/15/2023 | 1/20/2023 | 5 | IGKLM | 115 | Jan-2023 | 575 | 11/1/2022 |
1/29/2023 | 1/31/2023 | 2 | IDTHO | 115 | Jan-2023 | 230 | 2/25/2022 |
1/29/2023 | 1/31/2023 | 2 | IDTHO | 110 | Jan-2023 | 220 | 6/9/2022 |
Past_Reservations
arrival_date | departure_date | total_nights | rate_cat | rate | month_year | revenue | entered_on |
12/2/2021 | 12/10/2021 | 8 | IDMES | 115 | Dec-2021 | 920 | 11/22/2021 |
12/3/2021 | 12/10/2021 | 7 | IMMLR | 99 | Dec-2021 | 693 | 12/1/2021 |
12/5/2021 | 12/10/2021 | 5 | IGKLM | 110 | Dec-2021 | 550 | 11/20/2021 |
12/1/2021 | 12/15/2021 | 14 | IDMES | 115 | Dec-2021 | 1610 | 11/22/2021 |
12/7/2022 | 12/20/2021 | 13 | IMGOV | 95 | Dec-2021 | 1235 | 12/1/2021 |
12/18/2021 | 12/25/2021 | 7 | IMMLR | 105 | Dec-2021 | 735 | 12/5/2021 |
12/21/2021 | 12/24/2021 | 3 | IGKLM | 115 | Dec-2021 | 345 | 12/21/2021 |
12/25/2021 | 12/27/2021 | 2 | IDTHO | 109 | Dec-2021 | 218 | 4/10/2021 |
12/30/2021 | 1/5/2022 | 6 | IDTHO | 105 | Dec-2021 | 630 | 9/17/2021 |
1/1/2022 | 1/10/2022 | 9 | IMMLR | 105 | Jan-2022 | 945 | 12/12/2021 |
1/5/2022 | 1/10/2022 | 5 | IDMES | 110 | Jan-2022 | 550 | 12/12/2021 |
1/10/2022 | 1/13/2022 | 3 | IDMES | 112 | Jan-2022 | 336 | 1/1/2022 |
1/15/2022 | 1/20/2022 | 5 | IMGOV | 100 | Jan-2022 | 500 | 1/10/2022 |
1/20/2022 | 1/25/2022 | 5 | IMGOV | 105 | Jan-2022 | 525 | 1/10/2022 |
1/27/2022 | 1/30/2022 | 3 | IDTHO | 110 | Jan-2022 | 330 | 1/20/2022 |
1/31/2022 | 2/2/2022 | 2 | IDTHO | 115 | Jan-2022 | 230 | 1/31/2022 |
I have added couple of columns in power query.
The first one calculate the days difference between arrival_date and entered_on dates.
Other one divedes the first column values into group based on days.
The above matrix works fine with the FutureReservation data. I want to bring the data from Past_Reservation with the same arrival_date (but from last year).
I used the measure as follow:
LY_Past_Res_Rooms =
var _First_Arr_Date = FIRSTDATE('FutureReservations'[arrival_date])
var _LY_First_Arr_Date = DATE(YEAR(_First_Arr_Date)-1,MONTH(_First_Arr_Date),DAY(_First_Arr_Date))
var _Last_Dep_Date = LASTDATE('FutureReservations'[departure_date])
var _LY_Last_Arr_Date = DATE(YEAR(_Last_Dep_Date)-1,MONTH(_Last_Dep_Date),DAY(_Last_Dep_Date))
return
CALCULATE(COUNT('Past_Reservations'[rate_cat]),
FILTER(ALL('Past_Reservations'[arrival_date]),
'Past_Reservations'[arrival_date]>=DATEVALUE(_LY_First_Arr_Date)
&&
'Past_Reservations'[arrival_date]<DATEVALUE(_LY_Last_Arr_Date)
&&
'Past_Reservations'[arrival_date]<>BLANK()))
But it shows the error:
An argument or function DATE has wrong data type, or the result is too large or too small.
I have googled so many times and checked the other posts. But I coulnd't find the solution.
The same masuere works fine inside the Card visual. But not in the matrix above.
Please find the Pbix file here.
Need your help.
Thank you in advance.
Solved! Go to Solution.
@Devtr I see the issue, sometimes _First_Arr_Date is BLANK.
Try this:
LY_Past_Res_Rooms =
var _First_Arr_Date = FIRSTDATE('FutureReservations'[arrival_date])
return
if(_First_Arr_Date = BLANK(), BLANK(),
var _LY_First_Arr_Date = DATE(YEAR(_First_Arr_Date)-1,MONTH(_First_Arr_Date),DAY(_First_Arr_Date))
var _Last_Dep_Date = LASTDATE('FutureReservations'[departure_date])
var _LY_Last_Arr_Date = DATE(YEAR(_Last_Dep_Date)-1,MONTH(_Last_Dep_Date),DAY(_Last_Dep_Date))
var _Result = CALCULATE(COUNT('Past_Reservations'[rate_cat]),
FILTER(ALL('Past_Reservations'[arrival_date]),
'Past_Reservations'[arrival_date]>=DATEVALUE(_LY_First_Arr_Date)
&&
'Past_Reservations'[arrival_date]<DATEVALUE(_LY_Last_Arr_Date)
&&
'Past_Reservations'[arrival_date]<>BLANK()))
return
_Result
)
Hi,
Share the download link of the source Excel files as well - from where you loaded the data into PBI.
Hi,
See if my solution here helps. You may download the PBI file from here.
Hi @Ashish_Mathur Thank you for your time. Your approach does bring the result. But having tens of thousands of rows from both tables for multiple years will make this visual messy and confusing to the user. Not to mention that the same timeframe (that the user wants to look at) has multiple report dates as the month/year progresses. If you kindly look at my attempt and guide me with the solution to why the visual doesn't show all the rows/data from the past reservations table, I will be in debt to you.
Thanks.
You are welcome. I cannot understand your approach though i accept the shortcoming that you have identified in my approach.
@Devtr I am unable to recreate (see attached PBIX below signature). But, I don't know what group_bda is either so can't recreate 100%.
Sorry but I have attached the file already.
Did it not work?
Group_bda divides the booked_days_in_adv values into groups.
0 (reservation booked on same day)
1(one day ago)
...
6-10 (six to ten days ago)
...
Thanks.
@Devtr I see the issue, sometimes _First_Arr_Date is BLANK.
Try this:
LY_Past_Res_Rooms =
var _First_Arr_Date = FIRSTDATE('FutureReservations'[arrival_date])
return
if(_First_Arr_Date = BLANK(), BLANK(),
var _LY_First_Arr_Date = DATE(YEAR(_First_Arr_Date)-1,MONTH(_First_Arr_Date),DAY(_First_Arr_Date))
var _Last_Dep_Date = LASTDATE('FutureReservations'[departure_date])
var _LY_Last_Arr_Date = DATE(YEAR(_Last_Dep_Date)-1,MONTH(_Last_Dep_Date),DAY(_Last_Dep_Date))
var _Result = CALCULATE(COUNT('Past_Reservations'[rate_cat]),
FILTER(ALL('Past_Reservations'[arrival_date]),
'Past_Reservations'[arrival_date]>=DATEVALUE(_LY_First_Arr_Date)
&&
'Past_Reservations'[arrival_date]<DATEVALUE(_LY_Last_Arr_Date)
&&
'Past_Reservations'[arrival_date]<>BLANK()))
return
_Result
)
Thank you @Greg_Deckler for your quick reply and time.
As soon as I get a chance tomorrow, I will apply this.
I am pretty sure it will work.
Again I really appriciate your support and experise.
@Devtr Well, it fixes the issue you were having. Whether you get the result you want I don't know.
@Greg_Deckler I need your help again...
After changing the measure as suggested, the matrix shows the visual. But this measure shows the column total only. The row data is missing.
Past reservations have some reservations that fall under "2" and "6-10" groups. Because these reservations were booked that many days earlier.
So, some of the data from the past reservation is missing.
Thank you for your help and patiance.
@Devtr I missed the download link and just used the tables you posted, give me a minute.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
99 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
87 | |
64 |