Forum Discussion

sdhn's avatar
sdhn
Icon for Responsive Resident rankResponsive Resident
4 years ago
Solved

Subtract two date columns in existing table

Hi All,

 

I am using Power Bi Desktop to develop report.

 

I need to add new column that computes elapsed time (Resolved date  – Created date = Elapsed)

 

Both columns are date. Do I need to write mesaure ?  etc 

 

Please advise 

 

Thanks 

Help
  • sdhn's avatar
    sdhn
    4 years ago

    Elapsed =

    VAR _diff = [Resolved Date Time] - [Created]

    RETURN IF([Resolved Date Time]=BLANK(),0,_diff)

8 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    4 years ago

    Hi sdhn 

     

    Try this:

    Elapsed = [Resolved date]-[Created date]

     

    And change the new column data type to Whole number, Output:

     

     

     

     

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn:     www.linkedin.com/in/vahid-dm/

     

     

    • sdhn's avatar
      sdhn
      Icon for Responsive Resident rankResponsive Resident
      4 years ago

      Hi there ,

       

      Thanks 

      This seems work. Only issue when we have no value in Resolved date time gives some numbers as:

       

       

    • NotMyJob's avatar
      NotMyJob
      Icon for Helper III rankHelper III
      2 years ago

      Hi @sdhn , see the comment about about the error I get with that equation:  The value for 'Created Date' cannot be determined. Either the column doesn't exist, or there is no current row for this column.

  • sdhn's avatar
    sdhn
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Elapsed =

    VAR _diff = [Resolved Date Time] - [Created]

    RETURN IF([Resolved Date Time]=BLANK(),0,_diff)

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    4 years ago

    sdhn  you can create a measure like this

    difference = DATEDIFF(MAX('Table'[Created date]),MAX('Table'[Resolved date]),DAY)

    or  a calulated column

    Column = CONVERT('Table'[Resolved date]-'Table'[Created date],DOUBLE)

     whatever you prefer

     

  • sdhn's avatar
    sdhn
    Icon for Responsive Resident rankResponsive Resident
    4 years ago

    Hi there,

     

    thanks for your feedback. But I am not getting correct values.

     

    1)  

    Elapsed = CONVERT(MAX(Incident[Resolved Date Time])-MAX(Incident[Created]),DOUBLE)

     

    2)

    Elapsed Diff = DATEDIFF(MAX(Incident[Created]),MAX(Incident[Resolved Date Time]),DAY)
     

     

     

    Thanks 

    • NotMyJob's avatar
      NotMyJob
      Icon for Helper III rankHelper III
      2 years ago

      Yeah, I don't get that either with my table. 

       

      From: 

      Days Overdue = [Created Date] - [Due Date]
       
      I get the error:  The value for 'Created Date' cannot be determined. Either the column doesn't exist, or there is no current row for this column.