An argument or function DATE has wrong data type will not resolve

[Devtr]

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.

[Ashish_Mathur]

Hi,

Share the download link of the source Excel files as well - from where you loaded the data into PBI.

[Devtr]

Hey @Ashish_Mathur 

Here is the link to the whole folder.

Thank you.

[Ashish_Mathur]

Hi,

See if my solution here helps.  You may download the PBI file from here.

[Devtr]

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.

[Ashish_Mathur]

You are welcome.  I cannot understand your approach though i accept the shortcoming that you have identified in my approach.

[Greg_Deckler]

@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%.

[Devtr]

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_Deckler · Accepted 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
)

[Devtr]

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.

[Greg_Deckler]

@Devtr Well, it fixes the issue you were having. Whether you get the result you want I don't know.

[Devtr]

@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.

[Greg_Deckler]

@Devtr I missed the download link and just used the tables you posted, give me a minute.