Forum Discussion

SolarWill's avatar
SolarWill
Regular Visitor
7 years ago

Date/Time subtraction from columns in SharePoint list

Hello,

 

I have a SP list I brought into BI and I need to subtract the date & time from specific keywords in another column, see the snippet below:

 

 

I need to subtract the date & time when the "Status" column says "Open" and "In Progress" - you can ignore the "Closed" status.

 

So basically date & time from the "Created" column: 'Open date MINUS In Progress date' = "new column for return value" = As you can see in the first 3 entries for Ticket ID: 1000, there is a difference of about 4 minutes there.

 

I need the output to be in minutes, hours, then days if necessary.

 

How can I acheive this? Thanks.

Need Help

3 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    Hi SolarWill ,

     

    To create a measure as below.

     

    Measure = 
VAR odate =
    CALCULATE (
        MAX ( 'Table'[Created] ),
        FILTER ( 'Table', 'Table'[Status] = "Open" )
    )
VAR inprogress =
    CALCULATE (
        MAX ( 'Table'[Created] ),
        FILTER ( 'Table', 'Table'[Status] = "In Progress" )
    )
RETURN
    DATEDIFF ( odate, inprogress, MINUTE )

     

    • SolarWill's avatar
      SolarWill
      Regular Visitor
      7 years ago

      Hello, I tried your measure and adjusted for my table name - there are no syntax errors but when I add the measure all of a sudden all of the data dissappears:

       

       

      • v-frfei-msft's avatar
        v-frfei-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        Hi SolarWill ,

         

        Kindly share your PBIX to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.