cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Devtr
Helper I
Helper I

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.

Devtr_0-1669242286697.png

 

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.

 

1 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
)

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

12 REPLIES 12
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hey @Ashish_Mathur 

Here is the link to the whole folder.

Thank you.

Hi,

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

Untitled.png


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

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

 


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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.

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

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

Devtr_0-1669300820865.png

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.

 

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.