5 Replies Latest reply on Jul 24, 2018 12:14 PM by Naveen B

# datediff is giving wrong calculations in some cases

I have a datediff calculation as below. It gives correct calculation in most cases. But in some cases it is giving wrong results why?

DATEDIFF('day',[Start Date],[Target Date])

correct

wrong

i created a tabular view. First few rows represent wrong calculations.

i noticed that in case of wrong calculations, most of them have start date from Q1 of 2018 and end date Q2 and after.

• ###### 1. Re: datediff is giving wrong calculations in some cases

HI Nikhil,

if i am right datediff('day',startdate,enddate) is in measure

while you are dragging, it will be enclosed with sum(days) thats why you are getting days very high

Convert the field to discrete and dimension and use that field so that you will get the correct value

hope this helps kindly mark this answer as correct/helpful so that it will help others

BR,

NB

• ###### 2. Re: datediff is giving wrong calculations in some cases

Question:

Is your DateDiff stored as a dimension or a measure? If it's a measure then it is likely SUMMING based on the number of rows brought into your pane, thus throwing off your calculation.

Dimension:

Measure:

You can see in the latter it's summing based on the total number of records. Hopefully this is all it is!

Best,

Bryce

• ###### 3. Re: datediff is giving wrong calculations in some cases

i would like to keep the duration as a measure as i am using that field for sizing bars of the Gantt chart. Is there a way to change my formula (may be using LOD) so that i can still maintain that field as a measure?

• ###### 4. Re: datediff is giving wrong calculations in some cases

i would like to keep the duration as a measure as i am using that field for sizing bars of the Gantt chart. Is there a way to change my formula (may be using LOD) so that i can still maintain that field as a measure?

• ###### 5. Re: datediff is giving wrong calculations in some cases

Use the

max(datediff('day',startdate,enddate) will give the value and it will be in measure

hope this helps, kindly mark this answer as correct or helpful so that it will help others

BR,

NB

1 of 1 people found this helpful