Forum Discussion

inglexjc's avatar
inglexjc
Icon for Post Patron rankPost Patron
1 year ago
Solved

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 Fi...
Need Help
  • MarkLaf's avatar
    MarkLaf
    1 year ago

    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.

     

     

Omid_Motamedise's avatar
Omid_Motamedise
Icon for Super User rankSuper User
1 year ago

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"