Forum Discussion
An argument or function DATE has wrong data type will not resolve
- 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 )
Hi,
Share the download link of the source Excel files as well - from where you loaded the data into PBI.
- Ashish_Mathur3 years agoSuper User
- Devtr3 years agoHelper III
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_Mathur3 years agoSuper User
You are welcome. I cannot understand your approach though i accept the shortcoming that you have identified in my approach.