- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 03:24 PM

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

Solved! Go to Solution.

Accepted Solutions

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 04:33 PM

All Replies

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 03:44 PM - edited 03-17-2017 08:05 AM

@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= 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 )Column

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

Here's the result...

Hope this helps!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 04:18 PM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 04:25 PM

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!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-31-2017 04:33 PM

That worked! Thank you

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-06-2017 06:26 PM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-17-2017 06:07 AM

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?

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

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-17-2017 08:02 AM - edited 03-17-2017 08:04 AM

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= 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 )Measure

And here's the result...

Hope this helps!

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-20-2017 02:22 AM

Yes, Now it's working fine Thank you

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

02-07-2018 10:51 AM

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?