Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Coelijoeli
New Member

Days between two dates columns

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?

 

15 REPLIES 15
dramus
Continued Contributor
Continued Contributor

There's also the DATEDIFF DAX function.

 

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

gkhare
Frequent Visitor

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
Continued Contributor
Continued Contributor

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

 

Hello,

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

Have you ever seen like this:

Start dateEnd dateNo of days
2016.12.092017.09.04269
2016.12.092017.09.01532
2016.12.092017.08.311855
2016.12.092017.08.10244
2016.12.092017.07.311170
2016.12.092017.07.13216
2016.12.092017.06.301015

 

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

 

Do you have any idea?

dramus
Continued Contributor
Continued Contributor

Capture.JPG

 

 

 

 

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

dramus
Continued Contributor
Continued Contributor

Capture.JPG

 

 

 

 

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

amarines
Frequent Visitor

Hello, what is the expresion you used??

Thanks for posting - just what I was looking for

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.

 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
Continued Contributor
Continued Contributor

@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
Resolver III
Resolver III

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.

Hi,

 

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

 

Regards.

Akahn
Advocate IV
Advocate IV

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

Seth_C_Bauer
Community Champion
Community Champion

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


Looking for more Power BI tips, tricks & tools? Check out PowerBI.tips the site I co-own with Mike Carlo. Also, if you are near SE WI? Join our PUG Milwaukee Brew City PUG

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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