topic Re: Days between two dates columns in Service

Days between two dates columns

Coelijoeli — Tue, 29 Sep 2015 09:53:06 GMT

Hello community,

I'm building a dashboard with Power BI Desktop.

I've connected a SQL server database which has two date fields:

[Transactiondate]

[Closed]

What I want to do is get the difference between these two in days.

I tried a DAX measure to find the difference( Closed - Transactiondate), but this gives me a weird date value.

Can someone help me with this problem?

Re: Days between two dates columns

Anonymous — Tue, 29 Sep 2015 13:23:32 GMT

diff = 1.0*([Closed]-[TransactionDate])

Re: Days between two dates columns

Akahn — Tue, 29 Sep 2015 14:28:16 GMT

Are you trying to do this calculation in a measure or a calcluated column?

Re: Days between two dates columns

Rémi — Tue, 29 Sep 2015 16:22:22 GMT

Hi Coelijoeli,

When you do the difference, Power BI will keep the same format. Then you have to convert your result in order to obtain what you want.

Then you can add format function, or you can choose a number format into the top ribbon.

Re: Days between two dates columns

Haegi — Wed, 30 Sep 2015 12:03:40 GMT

Hi,

You can create a new measure with the dax formula by @Eno1978

Regards.

Re: Days between two dates columns

dramus — Wed, 30 Sep 2015 17:58:34 GMT

There's also the DATEDIFF DAX function.

Days between = DATEDIFF([Transactiondate],[Closed],DAY)

Re: Days between two dates columns

gkhare — Wed, 10 Feb 2016 08:50:50 GMT

Not working for me! I' am trying to take no.of days between close_date and start_date, it throws an error which says-The third argument Interval can only be one of the following: YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, SECOND...

I' am using the following expression

DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],(AA_SALES_FACT[START_DATE]),DAY())

Request all gurus to kindly help me in fixing the above. I need to take the difference between Close_Date and Create_Date

Re: Days between two dates columns

HarrisMalik — Wed, 10 Feb 2016 13:48:45 GMT

gkhare there is a mistake in your formula the third argument to DATEDIFF is Interval not function. Use following formula:

DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],AA_SALES_FACT[START_DATE],DAY)

Re: Days between two dates columns

HarrisMalik — Wed, 10 Feb 2016 13:51:06 GMT

gkhare there is a mistake in your formula the third argument to DATEDIFF is Interval not function. Use following formula:

DaysLenght = DATEDIFF(AA_SALES_FACT[CLOSE_DATE],AA_SALES_FACT[START_DATE],DAY)

Edit: I do not know your data model just to be sure the first argument of DATEDIFF() is Start Date, second is End date and third is interval i.e. DAY, MONTH,YEAR etc

DATEDIFF(<start_date>, <end_date>, <interval>)

Re: Days between two dates columns

WendyMc — Fri, 15 Apr 2016 12:47:42 GMT

Thanks for posting - just what I was looking for

Re: Days between two dates columns

MAPA — Fri, 09 Dec 2016 12:07:21 GMT

Hello,

I've used your suggestion which seem to be good but something is wrong there.

Have you ever seen like this:

Start date	End date	No of days
2016.12.09	2017.09.04	269
2016.12.09	2017.09.01	532
2016.12.09	2017.08.31	1855
2016.12.09	2017.08.10	244
2016.12.09	2017.07.31	1170
2016.12.09	2017.07.13	216
2016.12.09	2017.06.30	1015

When the end date is the last day of the month it gives wrong result. :(

Do you have any idea?

Re: Days between two dates columns

amarines — Thu, 20 Apr 2017 15:13:58 GMT

Hello, what is the expresion you used??

Re: Days between two dates columns

dramus — Wed, 11 Oct 2017 18:08:41 GMT

I can't reproduce your problem. Ar your columns defined as dates?

Re: Days between two dates columns

dramus — Wed, 11 Oct 2017 18:09:04 GMT

I can't reproduce your problem. Ar your columns defined as dates?

Re: Days between two dates columns

peymanrmt — Wed, 20 Jun 2018 16:06:19 GMT

Hi all,

How do we calculate the interval between two dates, while excluding the weekends and US holidays. We need this interval to be calculated only for business days, excluding weekends and holidays to have a good measure of performance. Any help is much appreciated. BTW, the interval should be calculated in hours, NOT days or months.

Any help is much appreciated.

Re: Days between two dates columns

peymanrmt — Wed, 20 Jun 2018 16:10:28 GMT

Hi all,

How do we calculate the time interval in hours between two dates while excluding the weekends and US holidays in the calendar? Any help is much appreciated.