Forum Discussion
Date Difference between 2 columns same table.
I'm trying to show the date difference between 2 columns in the same table. Exampe I'm wanting to show how many days between "Initial Ruling Date" and "Final Ruling Date"
| Initial Ruling Date | Final Ruling | Final Ruling Date |
| 6/5/2025 | Dismissed | 6/30/2025 |
| 4/14/2025 | Dismissed | 5/12/2025 |
| 6/30/2025 | Dismissed | 7/17/2025 |
| 4/15/2025 | Dismissed | 5/9/2025 |
| 3/26/2025 | Dismissed | 4/14/2025 |
| 6/13/2025 | Dismissed | 7/7/2025 |
| 3/11/2025 | Dismissed | 4/25/2025 |
| 4/24/2025 | Dismissed | 5/16/2025 |
| 3/28/2025 | Dismissed | 4/15/2025 |
| 3/31/2025 | Dismissed | 4/15/2025 |
| 2/12/2025 | Dismissed | 2/27/2025 |
| 4/24/2025 | Dismissed | 5/16/2025 |
Add a custom column and use Duration.TotalDays, like:
Duration.TotalDays( [Final Ruling Date] - [Initial Ruling Date] )When doing addition with dates, you'll get a duration as an output. This function converts that duration to # days.
Output would look like the following. Note that if the initial or final date are null, then the custom column will also be null. I added nulls to your initial data to show this.
6 Replies
Add a custom column and use Duration.TotalDays, like:
Duration.TotalDays( [Final Ruling Date] - [Initial Ruling Date] )When doing addition with dates, you'll get a duration as an output. This function converts that duration to # days.
Output would look like the following. Note that if the initial or final date are null, then the custom column will also be null. I added nulls to your initial data to show this.
Duration.TotalDays is returning decimal number that includes fractional days.
Would there be a reason to use this function for two columns that only contain dates?
When only dealing with dates, then Duration.TotalDays and Duration.Days provide the same result. Perhaps there is a (probably infinitesimal) performance edge with using Duration.Days?
I went with Duration.TotalDays as it is more precise when dealing with datetimes, so it's more general purpose.
E.g.,
Duration.Days( #duration(0,23,59,59) ) = 0
VS
Duration.TotalDays( #duration(0,23,59,59) ) = 0.99998842592592585
Hi inglexjc ,
Thanks for reaching out to the Microsoft fabric community forum.
Thanks for your prompt response
Another workaround to achieve this is by using Power Query, where you can simply select the two date columns and subtract one from the other to calculate the difference.
I have uploaded the file for your review. Please let us know if you need any assistance or modifications we’re happy to help.
We truly appreciate your continued engagement and thank you for being an active and valued member of the community.
If you're still experiencing any challenges, please don’t hesitate to reach out we’d be more than happy to assist you further.
We look forward to hearing from you.
Best regards,
LakshmiHello inglexjc
If you wanted to calculate this in Power BI with DAX, create a column with below DAX
DATEDIFF(
'Table'[Initial Ruling Date],
'Table'[Final Ruling Date],
DAY
)
Or, if you are on Import mode and would like to have them calculated during data load, in Power Query, Add one Custom Column with below M code:Duration.Days([Final Ruling Date] - [Initial Ruling Date])
This will also create a new column showing the difference in days.
Hope this helps:)
Hi inglexjc
the issue came from the format of your date, but using the "en-US") Culture can easily solve the problem.
just copy the following code and paste it into the advanced editor to see how
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jZAxCsAgDEXvklmIiUbbvbcQt3Zw6OT9oUKhKkToFsLL/09TgoCCbFnAwFHqXWq9zjYHdPbdZ5PAI3mNEiTuVL+ZqYgUpyy1UXDvkEMOGtRF3kJyemEco4j0KJbRilcvDJPWttCSkXKLxpHi7+9mqi3jfy/I+QE=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Initial Ruling Date" = _t, #"Final Ruling " = _t, #"Final Ruling Date" = _t]), #"Lowercased Text" = Table.TransformColumns(Source,{{"Final Ruling Date", each Date.FromText(_, "en-US"), type date},{"Initial Ruling Date", each Date.FromText(_, "en-US"), type date}}), #"Added Custom" = Table.AddColumn(#"Lowercased Text", "Custom", each Duration.Days([Final Ruling Date]-[Initial Ruling Date])) in #"Added Custom"
