cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper III

## Days between dates column not correctly computing

Hello

I am running two columns which cacluate days between dates and both are throwing incorrect results

1 ACCEPTED SOLUTION
Responsive Resident

Recreated your table and created two different calculated columns with the same results

```datediff =
IF (
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) > 0,
-1 * ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) ),
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)```

where

• 'Table2' should be replaced with your table and

Another Calculated column

```datediff 2 =
IF (
-1 * DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ),
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)```

These give the same result

7 REPLIES 7
Anonymous
Not applicable

Have you tried using the DATEDIFF function instead?

https://msdn.microsoft.com/en-us/library/dn802538.aspx

Helper III

I tried but doesnt help

Helper III

Responsive Resident

Recreated your table and created two different calculated columns with the same results

```datediff =
IF (
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) > 0,
-1 * ( DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ) ),
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)```

where

• 'Table2' should be replaced with your table and

Another Calculated column

```datediff 2 =
IF (
-1 * DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY ),
DATEDIFF ( 'Table2'[RequiredDate], Table2[AnswerDate], DAY )
)```

These give the same result

Helper III

Thanks everyone for stepping in

Yes my filter context in the table was incorrect which was giving inconsistent results

The forumlas provided below work great too

Anonymous
Not applicable

The formula does look like it produces the correct answer sometimes, but not all the time.

In your table, are you summarizing your Date fields in any way?  For example are you showing "First" or "Last" date for any reason?  I'm wondering if your data is in such a way that your formula is calculating using a different context to how your matrix is displaying the rows.

Anonymous
Not applicable

Can you share some sample data, what result you are getting and what result you are expecting?

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors