Solved: Re: invalid date representation

kfarynowski · ‎04-01-2016

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

kfarynowski · ‎04-04-2016

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.

View solution in original post

MattAllington · ‎04-01-2016

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.

* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

kfarynowski · ‎04-04-2016

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.