Forum Discussion

leroy773's avatar
leroy773
Icon for Helper II rankHelper 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 dur...
  • 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

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

leroy773's avatar
leroy773
Icon for Helper II rankHelper II
10 years ago

Thanks for the recommendation currently working with your proposal.  Unfortunately datediff is not recognized.  Checking the format of my dates, but continues to state un recognized.

  • leroy773's avatar
    leroy773
    Icon for Helper II rankHelper II
    10 years ago

    Found my mistake for the datediff, was using it in power query instead of the BI.  The datediff function is recognized but now fails for the start date cannot be greater than the end date.  Still investigating.