Forum Discussion

pswain's avatar
pswain
Helper I
5 years ago
Solved

Calculate Difference Between Two Date/Time Values in a new column

 

 

Hi All,

 

I have 2 column below, I would like to have the diffrence in days hours minutes like 2 days 12 hours 6 minutes... I am new to powerbi, can someone help me to provide me step by step process, I would really appreciate it. Thanks have  a good day.

 

 

Regards,

Prasanjit

 

  • PhilipTreacy's avatar
    PhilipTreacy
    5 years ago

    Hi pswain 

    In Power Query create a new Conditional Column and subtract Closed Month from Creation Month.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Creation Month", type datetime}, {"Closed Month", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [Closed Month]-[Creation Month])
in
    #"Added Custom"

     

    Sample file : Duration

    Phil 

    If I answered your question please mark my post as the solution.

    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • DavisBI's avatar
    DavisBI
    5 years ago

    Hi, pswain ,

     

    You just need to copy this to your new calculated column:

     

     

     

    DIFF = 
VAR _D = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],DAY)
VAR _H = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],HOUR)-_D*24
VAR _M = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],MINUTE)-_D*24*60-_H*60
RETURN
_D & " days" & _H & " hours" & _M & " minutes"

     

     

     

     Best.

3 Replies

  • manikumar34's avatar
    manikumar34
    Solution Sage
    5 years ago

    pswain , 

     

    Use DATEDIFF function to get the required output.

     

    Regards, 

    Manikumar

     

    Appreciate your Kudos. Accept as solution if this is helpful

  • PhilipTreacy's avatar
    PhilipTreacy
    Super User
    5 years ago

    Hi pswain 

    In Power Query create a new Conditional Column and subtract Closed Month from Creation Month.

     

    let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Creation Month", type datetime}, {"Closed Month", type datetime}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Duration", each [Closed Month]-[Creation Month])
in
    #"Added Custom"

     

    Sample file : Duration

    Phil 

    If I answered your question please mark my post as the solution.

    If my answer helped solve your problem, give it a kudos by clicking on the Thumbs Up.

  • DavisBI's avatar
    DavisBI
    Solution Specialist
    5 years ago

    Hi, pswain ,

     

    You just need to copy this to your new calculated column:

     

     

     

    DIFF = 
VAR _D = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],DAY)
VAR _H = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],HOUR)-_D*24
VAR _M = DATEDIFF('YourTable'[Closed Month],'YourTable'[Creation Month],MINUTE)-_D*24*60-_H*60
RETURN
_D & " days" & _H & " hours" & _M & " minutes"

     

     

     

     Best.