DAX DATEDIFF not working in some rows?

[Anonymous]

Hi,

I am trying to do a simple DATEDIFF between 2 date/time columns, but to my surprise it does work for the majority of the rows but not in all of them:

How is it possible that it is giving the right value sometimes and a wrong value in other rows? it is just a simple DATEDIFF calculated column

Thanks!

[az38]

Hi @Anonymous 

Are you sure you have no duplicate rows? it looks like TimeToCreate has an aggregation in visual Values properties. Try to set Don't summarize

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin

[Anonymous]

Yeah, I am sure, the Issue column is unique. Here you can see one of the wrong values in the data view:

Thanks

[az38]

@Anonymous 

try to debug - create columns

=DAY(CreateDate)

=DAY(OccuredDate)

=YEAR(CreateDate)

=YEAR(OccuredDate)

=MONTH(CreateDate)

=MONTH(OccuredDate)

=HOUR(CreateDate)

=HOUR(OccuredDate)

and so on, then check if data in those fiels is correct 

[Anonymous]

wow,

It seems that in the createdate column the month is the day and the day is the month, like if it was reading the date in US format. It's really strange because in the query editor I have both columns with date/time format. What can I do to solve this?

Thanks

[az38]

@Anonymous 

it explains why your calculate works good within one day, but doesnt work with midnight inside the period

so, try to mix up the things

=DATEDIFF(
datevalue(concatenate(concatenate(day([Occured_Date]);"/");concatenate(MONTH([Occured_Date]);format([Occured_Date];"/YYYY HH:mm:ss"))));
datevalue(concatenate(concatenate(day([Create_Date]);"/");concatenate(MONTH([Create_Date]);format([Create_Date];"/YYYY HH:mm:ss"))));
SECONDS
)
/360

or replace DAY and MONTH functions

but the best way is to change format in the data source

do not hesitate to kudo useful posts and mark solutions as solution
Linkedin