Forum Discussion

Devtr's avatar
Devtr
Helper III
3 years ago
Solved

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_datedeparture_datetotal_nightsrate_catratemonth_yearrevenueentered_on
12/2/202212/5/20223IDMES125Dec-202237511/20/2022
12/2/202212/3/20221IGKLM105Dec-202210511/1/2022
12/4/202212/8/20224IMMLR101Dec-202240411/1/2022
12/4/202212/5/20221IDTHO119Dec-202211910/20/2022
12/6/202212/10/20224IMMLR105Dec-202242011/20/2022
12/4/202212/8/20224IGKLM120Dec-202248011/15/2022
12/3/202212/5/20222IDMES129Dec-202225811/15/2022
12/30/20221/2/20233IMGOV100Dec-202230011/22/2022
1/2/20231/10/20238IDMES119Jan-202395211/23/2022
1/15/20231/20/20235IGKLM115Jan-202357511/1/2022
1/29/20231/31/20232IDTHO115Jan-20232302/25/2022
1/29/20231/31/20232IDTHO110Jan-20232206/9/2022

 

Past_Reservations

arrival_datedeparture_datetotal_nightsrate_catratemonth_yearrevenueentered_on
12/2/202112/10/20218IDMES115Dec-202192011/22/2021
12/3/202112/10/20217IMMLR99Dec-202169312/1/2021
12/5/202112/10/20215IGKLM110Dec-202155011/20/2021
12/1/202112/15/202114IDMES115Dec-2021161011/22/2021
12/7/202212/20/202113IMGOV95Dec-2021123512/1/2021
12/18/202112/25/20217IMMLR105Dec-202173512/5/2021
12/21/202112/24/20213IGKLM115Dec-202134512/21/2021
12/25/202112/27/20212IDTHO109Dec-20212184/10/2021
12/30/20211/5/20226IDTHO105Dec-20216309/17/2021
1/1/20221/10/20229IMMLR105Jan-202294512/12/2021
1/5/20221/10/20225IDMES110Jan-202255012/12/2021
1/10/20221/13/20223IDMES112Jan-20223361/1/2022
1/15/20221/20/20225IMGOV100Jan-20225001/10/2022
1/20/20221/25/20225IMGOV105Jan-20225251/10/2022
1/27/20221/30/20223IDTHO110Jan-20223301/20/2022
1/31/20222/2/20222IDTHO115Jan-20222301/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

    • Devtr's avatar
      Devtr
      Helper 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_Deckler's avatar
        Greg_Deckler
        Community Champion

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

  • Hi,

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