Forum Discussion
invalid date representation
I've created at Holiday table in excel, trying to use to determine work days between two dates. Looks like everywhere the date column is date format (both in the excel file, and after importing) but I get error in PowerBi desktop of Invalid representation of date in the statement for the Table1[Date] field.
CalcBusDays = CALCULATE(sum(Table1[IsWorkDay]),DATESBETWEEN(Table1[Date],Query[FileDate],Query[DateEntered]))
Thanks for the help. Table1 is a calendar table, but I found that the first date being passed was earlier than the first date in the table. The message indicating "invalid date representation" seemed like there was a problem with the data format of the column in the calendar table. I further reviewed the table with the dates being passed and found the unexpected dates. I expanded the calender table and all worked.
2 Replies
Is table1 a calendar table? Datesbetween is designed to be used over calendar tables. You need to pass a start date and an end date, but you are passing columns of dates - you can't do that. Try wrapping the 2 query columns in MIN for the starting column and MAX for the end column.
Thanks for the help. Table1 is a calendar table, but I found that the first date being passed was earlier than the first date in the table. The message indicating "invalid date representation" seemed like there was a problem with the data format of the column in the calendar table. I further reviewed the table with the dates being passed and found the unexpected dates. I expanded the calender table and all worked.
