Calenderauto function shows 1899 results after Datetime.localnow is set to time only

Dear Readers,

After I finished my dashboard I wanted to add a 'Refresh date/time'.

Used a blank query in the Query Editor with '= DateTime.LocalNow()'

The moment I duplicate it and Transform it to 'Time Only' with this result:

Once I close and apply my dashboard goes from:

to:

I narrowed the problem down to the Refresh Time column.

I have a column with date only that doesn't give this problem.

Once I delete the column Refresh time the Calenderauto(12) function returns a start date of January 2017 again.

I also made sure to deselect the time intelligence 'Auto date/time' setting.

Anybody has a way to avoid this wierd interaction with 'Time Only' and Calenderauto()?

Thanks in advance,

Luuk

Luuky
4 years ago
I solved the problem by not changing the Refresh Time type in the Power Query Editor. Instead I loaded the data in the Power BI Desktop and went to Data. Here I changed the Data type of the column Refresh Time to Time. Changing the datatype here didn't mess with the CALENDARAUTO(12) what fixed the problem. amitchandak so with your tip I managed to fix it.

amitchandak
Super User
4 years ago
Luuky , When you do time only in power query it loses date part and in DAX that 1899-12-31 and you time. That is why it is happening
- Luuky
  Helper I
  4 years ago
  amitchandak Thanks for responding. Is there a way to avoid it? Or just change the type in a later stage?
Luuky
Helper I
4 years ago
I solved the problem by not changing the Refresh Time type in the Power Query Editor. Instead I loaded the data in the Power BI Desktop and went to Data. Here I changed the Data type of the column Refresh Time to Time. Changing the datatype here didn't mess with the CALENDARAUTO(12) what fixed the problem. amitchandak so with your tip I managed to fix it.

Forum Discussion