## Desktop

Frequent Visitor
Posts: 15
Registered: ‎01-26-2017

# Measure to Calculate the number of days between two date columns

I am trying to do something that is very easy in Excel but haven't found the correct syntax in Power BI yet.

I need to create a measure that calcualtes the number of days between two dates.  I am not looking to do anything special like network days just plain old calendar days between dates in Column A to dates in Column B.  What is the simpliest way to calulate this in Power BI?

Thanks.

Judie

Accepted Solutions
Frequent Visitor
Posts: 15
Registered: ‎01-26-2017

## Re: Measure to Calculate the number of days between two date columns

That worked!  Thank you

All Replies
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Measure to Calculate the number of days between two date columns

[ Edited ]

@jb123  That would be the DATEDIFF function => DATEDIFF ( <star_date>, <end_date>, <interval> )

An error is returned if start_date is larger than end_date.

To avoid this error use this formula in a Calculated Column

```Days Column =
SWITCH (
TRUE (),
'Table'[start_date] < 'Table'[end_date], DATEDIFF ( 'Table'[start_date], 'Table'[end_date], DAY ),
'Table'[start_date] > 'Table'[end_date], DATEDIFF ( 'Table'[end_date], 'Table'[start_date], DAY ) * -1,
0
)```

You multiply * -1 to get negative numbers where the event ended before it started (implying time travel )

Here's the result...

Hope this helps!

Frequent Visitor
Posts: 15
Registered: ‎01-26-2017

## Re: Measure to Calculate the number of days between two date columns

What is the correct syntax?  Assume I am trying to create a Column and have the number of days between the dates in two columns.  DateDiff requires an interval like month, year, day, etc.  When I try to create a column using the following

Column = DATEDIFF(Table1[Dates1], Table1[Dates2], DAY)

I get an error that the start date cannot be greater than the end date.  I switch the order of dates columns and it still returns the same error.

Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Measure to Calculate the number of days between two date columns

Even if you have only 1 row where the end_date is before the start_date you'll get an error for the entire column!

So use the above formula and the negative numbers in the results will show you where that error(s) is(are).

Good Luck!

Frequent Visitor
Posts: 15
Registered: ‎01-26-2017

## Re: Measure to Calculate the number of days between two date columns

That worked!  Thank you

Community Support Team
Posts: 3,482
Registered: ‎09-27-2016

## Re: Measure to Calculate the number of days between two date columns

Hi @jb123,

Please mark the right reply as answer, so the people who have similar issue can find solution easily and clearly. Thanks a lot.

Best Regards,
Angelia

Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

## Re: Measure to Calculate the number of days between two date columns

I am not sure how it's working for you but when applied exactly the same formula for a new measure it does not allow me to put the column names like 'start date' and 'end date'. It only accept the formula or measure already created...Any suggestions?

Highlighted
Super Contributor
Posts: 2,029
Registered: ‎08-11-2015

## Re: Measure to Calculate the number of days between two date columns

[ Edited ]

@kb

The above formula is for a Calculated Column

When you try to create a Measure

Intellisense does not allow you to select a column because you need an aggregator first such as MIN or MAX

So here is the Measure formula

```Days Measure =
SWITCH (
TRUE (),
MIN('Projects'[start_date]) < MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[start_date]), MIN('Projects'[end_date]), DAY ),
MIN('Projects'[start_date]) > MIN('Projects'[end_date]), DATEDIFF ( MIN('Projects'[end_date]), MIN('Projects'[start_date]), DAY )* -1
)```

And here's the result...

Hope this helps!

Frequent Visitor
Posts: 4
Registered: ‎03-17-2017

## Re: Measure to Calculate the number of days between two date columns

Yes, Now it's working fine Thank you

Regular Visitor
Posts: 32
Registered: ‎11-01-2017

## Re: Measure to Calculate the number of days between two date columns

I am trying to use your basic formula, my version is :

```Days Last Update =
SWITCH (
TRUE (),
MIN('Production Approval Workflow'[Status_InFlight])=TRUE, DATEDIFF (MIN('Production Approval Workflow'[Modified]), today(), DAY ),
MIN('Production Approval Workflow'[Status_InFlight])=FALSE, ""
)```

But when I drop it into a table, I get the "Can't display the visual" error.

Note: The field [Modifed] iis a date/time stamp.

Also - why don't the table names and field values prepopulate when I am creating a formula?