Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
There's also the DATEDIFF DAX function.
Days between = DATEDIFF([Transactiondate],[Closed],DAY)
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
@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 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?
I can't reproduce your problem. Ar your columns defined as dates?
I can't reproduce your problem. Ar your columns defined as dates?
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.
@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)
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.
Are you trying to do this calculation in a measure or a calcluated column?
diff = 1.0*([Closed]-[TransactionDate])
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
38 | |
27 | |
17 | |
16 | |
8 |
User | Count |
---|---|
45 | |
38 | |
35 | |
17 | |
16 |