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_date departure_date total_nights r...
Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    3 years ago

    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
)

     

Greg_Deckler's avatar
Greg_Deckler
Community Champion
3 years ago

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's avatar
Devtr
Helper III
3 years ago

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's avatar
    Greg_Deckler
    Community Champion
    3 years ago

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

    • Devtr's avatar
      Devtr
      Helper III
      3 years ago

      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.