Forum Discussion

leroy773's avatar
leroy773
Helper II
10 years ago
Solved

Calculate duration based on dates in different rows.

All,

 

I am attempting to see if Power BI is a good fit.  And trying to get started on reporting.  I have data from salesforce I reported in the query.  I am looking for an easy way to show the duration between dates and report the value associated with it.  In excel it is pretty starightforward, but looking for a more automated way in BI.  Since Ihave to update the file and subtract from the current date for the first row.  Any help will be greatly appreaciated.  In excel I added the duration and just copied the Status column

 

Edit DateOld ValueNew ValueSerialdurationStatus
10/19/15 8:06 AMDown for MaintenanceFully Operational1300.66Fully Operational
10/14/15 9:11 AMFully OperationalDown for Maintenance14.954861111Down for Maintenance
7/21/15 8:06 AMNon-OperationalFully Operational185.04513889Fully Operational
7/15/15 12:35 PMFully OperationalNon-Operational15.813194444Non-Operational
7/7/15 2:10 PMNon-OperationalFully Operational17.934027778Fully Operational
7/6/15 5:09 PMFully OperationalNon-Operational10.875694444Non-Operational
5/20/15 7:13 AMNon-OperationalFully Operational147.41388889Fully Operational
5/18/15 8:42 AMFully OperationalNon-Operational11.938194444Non-Operational
4/15/15 6:40 AMCustomer SituationFully Operational133.08472222Fully Operational
4/14/15 4:36 PMNon-OperationalCustomer Situation10.586111111Customer Situation
4/14/15 6:12 AMFully OperationalNon-Operational10.433333333Non-Operational
3/26/15 7:36 PMNon-OperationalFully Operational118.44166667Fully Operational
3/25/15 1:37 PMFully OperationalNon-Operational11.249305556Non-Operational
7/20/16 8:42 PMReduced ThroughputFully Operational225.14Fully Operational
7/15/16 12:11 PMNon-OperationalReduced Throughput25.354861111Reduced Throughput
7/15/16 7:54 AMFully OperationalNon-Operational20.178472222Non-Operational
6/18/16 4:31 AMNon-OperationalFully Operational227.14097222Fully Operational
6/17/16 5:42 AMFully OperationalNon-Operational20.950694444Non-Operational
6/14/16 2:07 PMNon-OperationalFully Operational22.649305556Fully Operational
6/10/16 7:27 AMFully OperationalNon-Operational24.277777778Non-Operational
6/8/16 4:01 PMNon-OperationalFully Operational21.643055556Fully Operational
6/7/16 1:05 PMFully OperationalNon-Operational21.122222222Non-Operational
5/31/16 11:58 AMNon-OperationalFully Operational27.046527778Fully Operational
5/25/16 6:57 AMFully OperationalNon-Operational26.209027778Non-Operational
2/26/16 5:51 AMReduced ThroughputFully Operational289.04583333Fully Operational
2/25/16 5:50 AMNon-OperationalReduced Throughput21.000694444Reduced Throughput
  • Anonymous's avatar
    Anonymous
    10 years ago

    Hi leroy773,


    Based on your description, you want to compare your current row with previous row, right? If that is the case, firstly, go to query editor of Power BI Desktop and add an index column in your current table.

    Secondly, add a new column and write DAX formula to compare date values of the rows.

    Duration = DATEDIFF(Table5[Column1],IF(Table5[Index]=0,Table5[Column1],LOOKUPVALUE(Table5[Column1],Table5[Index],Table5[Index]-1)),HOUR)/24


    For more details, you can review the example in the attached PBIX file. 



    Thanks,
    Lydia Zhang

11 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    leroy773 in power bi desktop easiest way to do is go to query editor, under Add Column tab -> Add Custom Coumn which will give you dialog box to enter power query. you can simply drag and drop your start and end date columns and subtract them. this will create a step in power bi desktop which will be applied each time you refresh the query. If your column type of start and end date is date/time then resulting column will also be date time.

     

     

    • leroy773's avatar
      leroy773
      Helper II
      10 years ago
      Thanks for the feedback, but unfortunately I only have one date in each row. So need to be able to subtract from previous row. But that tip is useful for other items.
      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi leroy773,


        Based on your description, you want to compare your current row with previous row, right? If that is the case, firstly, go to query editor of Power BI Desktop and add an index column in your current table.

        Secondly, add a new column and write DAX formula to compare date values of the rows.

        Duration = DATEDIFF(Table5[Column1],IF(Table5[Index]=0,Table5[Column1],LOOKUPVALUE(Table5[Column1],Table5[Index],Table5[Index]-1)),HOUR)/24


        For more details, you can review the example in the attached PBIX file. 



        Thanks,
        Lydia Zhang

    • Sagarkansal's avatar
      Sagarkansal
      Advocate I
      8 years ago

      Thanks Ankit...forgot that Power BI is not similar to Excel in Formula ease.