Forum Discussion
Calculate duration based on dates in different rows.
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 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.
- 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.
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 ZhangThanks 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.
Thanks Ankit...forgot that Power BI is not similar to Excel in Formula ease.
