PowerBI/DAX: Unable to correctly compare two dates

Question

I have this custom date that I created as a measure:

Start Date = DATE(YEAR(MAX(Loss[dte_month_end]))-1,12,31)

So this part looks fine in PowerBI and seems to be the right format.

So now I created a new column where I'm going through my data to check whether a record is equal to my "Start Date" as defined above.

IsStart = IF(Loss[dte_month_end]=[Start Date], TRUE, FALSE)

but the weird thing is that all records are evaluated to false. I know this is actually not the case in my actual data, and I could find actual records with dte_month_end = 12/31/2017 as shown above.

Can someone help me understand why the IF statement would not be able to evaluate this correctly? I initially thought that this may be a case of the DATETIME format being inconsistent - but I purposefully changed both formats to be the same to no avail.

Thanks.

Edit1----------- FYI: This is the format that my dte_month_end field has:

Edit2 -- I tried changing the dte_month_end format to Date instead of DateTime, and it still doesn't seem to work:

It does sound like a Date vs DateTime issue to me. Your Start Date is definitely a Date type, so double check [dte_month_end] is a Date type as well. — Alexis Olson
@AlexisOlson Yup - it's definitely a DateTime field too. I think as a double check, if it isn't, I wouldn't be able to use the YEAR() function on it for the Start Date measure without it giving me an error. I guess the real question for me is that if they're both date/time fields, and I've set it so that they both have the same format - what else could be causing it to evaluate incorrectly? — Farellia
Start Date is defined as a Date field, not a DateTime field. You don't want to match that with a DateTime field. — Alexis Olson
@AlexisOlson That's fair - I just changed the Data type to be Date for dte_month_end, and it's still not evaluating correctly. I'm doing it in the "Modeling" tab - so maybe that's not at the right area? If not, is there another place for me to do it? — Farellia

Alexis Olson Alexis Olson · Accepted Answer · 2018-02-27T19:22:48

This is happening because you are using a measure inside of a calculated column. When you do this, the filter context for the measure is the row context in the table.

To fix this, you need to modify the filter context for your measure. For example:

Start Date = DATE(YEAR(CALCULATE(MAX(Loss[dte_month_end]), ALL(Loss))) - 1, 12, 31)

or

Start Date = DATE(YEAR(MAXX(ALL(Loss), Loss[dte_month_end])) - 1, 12, 31)

If you don't do this, the MAX only looks at the current row, rather than all the rows in the table.

PowerBI/DAX: Unable to correctly compare two dates

1 Answers