Date not calculating correctly

[Anonymous]

Hi everyone,

I'm having a strange issue with a calculated date column and I was hoping you could help. I have a date, month, and year that I then consolidate into one column called "survey end date". The below example is showing the correct answer of 31/07/15:

Here's how the column is calculated:

The strange thing is that some answers are showing correctly, and some have the month and day switched around. In the below section of the results it's showing 04/09/19 instead of 09/04/19:

I can't think of what would cause this? They're from within the same data set so I can't see why some answers would be right and some wrong? The individual date, month, and year columns are formatted as whole numbers, and the Survey End Date column is formatted as date (dd/mm/yy).

Any help would be greatly appreciated!

Thanks

[jdbuchanan71 · Accepted solution]

Give this measure a try instead.

Survey end date = DATE ( Sheet[Survey end year], Sheet[Survey end month], Sheet[Survey end date])

[Anonymous]

Thanks so much - that seems to have fixed it!

[Anonymous]

Hey @Anonymous 

I have a theory.

When the day is 31 (or above 12) then it easy to know it is a day.

When it is 9 (or equal/below 12) the engine gets confused.

Try to wrap your code with the FORMAT() function.

Something like

Format(DATE,"dd/MM/yyy")

Let us know if it worked.

Thanks!
A