cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
calum_haddow Frequent Visitor
Frequent Visitor

Date not calculating correctly

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:

 

Correct columns.PNG

 

 

Here's how the column is calculated:

 

Formula.PNG

 

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:

 

Columns.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Date not calculating correctly

Give this measure a try instead.

Survey end date = DATE ( Sheet[Survey end year], Sheet[Survey end month], Sheet[Survey end date])
3 REPLIES 3
AClerk New Contributor
New Contributor

Re: Date not calculating correctly

Hey @calum_haddow 

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

Super User
Super User

Re: Date not calculating correctly

Give this measure a try instead.

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

Re: Date not calculating correctly

Thanks so much - that seems to have fixed it!

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 39 members 1,319 guests
Please welcome our newest community members: