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
)

     

Devtr's avatar
Devtr
Helper III
3 years ago

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's avatar
Ashish_Mathur
Super User
3 years ago

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