Date column is not a date?

[Anonymous]

Hi,

I'm trying to create a calculated column which is based on whether a date field is less than another date value.  However, my date field is not behaving like a date and I'm not sure how to troubleshoot anymore (I've been staring at it all day)  😞

My formula is as follows:  

Calculated column:  Current Employee = IF(Datevalue(Employees[createDate]) < [maxDate], true, false)

Measure:  maxDate = max('calendar'[Date])

The result is that 'Current Employee' is set to true for all records.

When I add a column to test the value of [maxDate] (subtract dates), I get weird results, which tells me [maxDate] is not actually a date value (at least I don't think it is).

I've checked that maxDate is of type date, calendar[date] is of type date, createDate is of type date.  I do not have a relationship between Employees and calendar (and cannot put one on as it will not return the correct recordset).

Anyone have any ideas as to what else I can look into, as to why my date field is not behaving properly?

Thanks

[MFelix]

Hi @Anonymous ,

Just looking at the table you are returning it appears to me that the create date column is not on the date format, can be wrong and just be a formatting of the date when compare to the MAXDATE measure.

Can you share a sample file?

Regards,

MFelix

[TomMartens]

Hey,

I do not fully understand what you want to achieve ...

But, you can't (at least not in most of the cases) use a measure within a calculated column.

This is basically due to the timing of evaluation. Basically calculated columns are getting evaluated just once, this is when the data will be refreshed. in contrast to this, a measure will be evaluated whenever the user interacts with the data, e.g. selceting an item from a slicer or he interacts in other ways with the data. This means the measure maxDate will be evaluated just once, when calculated column inside the table will be "refreshed", and besides that "Power BI Desktop" assumes that no interaction has been happened. This assumption results to "latest" date from your calendar table.

Hopefully, this provides a starting point for rethinking your solution. if maxdate is dependent on user interaction you also have to make "current employee" a measure and filter the data using measures.

Regards,

Tom