Forum Discussion
An argument or function DATE has wrong data type will not resolve
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.
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 )
12 Replies
- Greg_DecklerCommunity Champion
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%.
- DevtrHelper III
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.
- Greg_DecklerCommunity Champion
Devtr I missed the download link and just used the tables you posted, give me a minute.
- Ashish_MathurSuper User
Hi,
Share the download link of the source Excel files as well - from where you loaded the data into PBI.
- DevtrHelper III
- Ashish_MathurSuper User