Forum Discussion
Helper IICalculate 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
Helper IIIs there a way in power BI to offset the calucation to the previous row. Currently duration shows up in the row, but would like to offset the calculation to show the duration in the previous row. Currently 69 is in the first row, but would like that to show up in the second and row.
| SN | Edit Date | Old | New | Index | Duration | |
1 | 8/16/2016 13:14 | Operational Status | Fully Operational | Non-Operational | 0 | 69 |
| 1 | 6/8/2016 7:24 | Operational Status | Reduced Throughput | Fully Operational | 1 | 8 |
| 1 | 5/31/2016 12:54 | Operational Status | Fully Operational | Reduced Throughput | 2 | 8 |
| 1 | 5/23/2016 9:35 | Operational Status | Reduced Throughput | Fully Operational | 3 | 7 |
| 1 | 5/16/2016 16:02 | Operational Status | Fully Operational | Reduced Throughput | 4 | 53 |
| 1 | 3/24/2016 9:14 | Operational Status | Non-Operational | Fully Operational | 5 | 0 |
Memorable MemberI don't follow what you mean by 'calculate' ..... but Lydia gave method in post on how to join another row's value using LookupValue and (Index-1)
not sure if the right solution is -1 or +1 but in any case it is the concept to use
- leroy773
Thanks got everything working now, just need to fine tune the query