Date column doesn't seem to be recognized as Date type in calculation

[kbuckvol]

I have a reshaped table which has a date type column that I want to use in a calculated running total formula. The problem is that my "Milestone Date" column looks like a date column but it's not behaving as one (no date heiarchy, for example) and it's giving me a the following error when I try to use it in a FILTER/ALLEXCEPT type calculation: "EARLIER/EARLIEST refers to an earlier row context which doesn't exist."

Something seems fishy here!

[SParker]

Hi @kbuckvol  did you ever find a solution to this problem? I have the exact same problem and am looking for a solution. Thanks in advance!

[bittus]

Hi,

I had the same issue and found a solution (in my case). It was just a parameter in the options of PBI Desktop which was not activated.

The trick is that this parameter exists at a global level and for each pbix file as well.

You can verify in

• option>global>data load>time intelligence> auto date/time
• option>current file>data load>time intelligence> auto date/time

In my case, the option was simply deactivated at the file level.

[BobBI]

Hi ,

It looks like data type for this columns in generic in power query ,

Power Query

If you change data type to Date ( right click on the column-->change type--> Date) in power query , would fix this issue.

Give it a try mate.

Thanks,

Sukhi

[kbuckvol]

Thanks for your reply, Sukhi!

The native column is definitely a date type in the source table.  In the query editor, it also shows as a date type as well.  As I understand it, PowerQuery is an Excel add-in which I’m not using.

[HotChilli]

Do you have a date table and is 'milestone date' related to it? If so, the features of the date 'jump' to the date table, if that makes sense.

[kbuckvol]

It is not a related. It IS, however, a combination of two other date columns created from both an unpivot and an append of two tables.  But each step of the query shows these columns as a date type column in the query editor.

[amitchandak]

If possible please share a sample pbix file after removing sensitive information.Thanks.

My Recent Blog -

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[kbuckvol]

I'd really like to share the tbale but since it's a financial table, the amount of editing/cleaning would render the table something really different than what I'm actually working with.