Days between two dates columns

[Coelijoeli]

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?

[dramus]

There's also the DATEDIFF DAX function.

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

[gkhare]

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

[HarrisMalik]

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

[MAPA]

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?

[dramus]

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

[dramus]

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

[amarines]

Hello, what is the expresion you used??

[WendyMc]

Thanks for posting - just what I was looking for

[peymanrmt]

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.

[peymanrmt]

 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.

[HarrisMalik]

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

[Rémi]

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.

[Haegi]

Hi,

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

Regards.

[Akahn]

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

[Seth_C_Bauer]

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