calculate difference consecutive rows

2 Topics

Calculate start and end for each status
Hi guys! I've been having a problem, and I believe the solution is quite simple. I have a table that contains data for each 5 minute interval. The data in this table extends three years. What I need to do is calculate the start and end of each status while keeping their order in mind, and then calculate the duration of this calculated period. Here's an illustration: Date and time Status Duration (What I need) 05/20/2022 01:05 AM OFF 15 minutes 05/20/2022 01:10 AM OFF 15 minutes 05/20/2022 01:15 AM OFF 15 minutes 05/20/2022 01:20 AM ON 15 minutes 05/20/2022 01:25 AM ON 15 minutes 05/20/2022 01:30 AM ON 15 minutes 05/20/2022 01:35 AM OFF 10 minutes 05/20/2022 01:40 AM OFF 10 minutes 05/20/2022 01:45 AM ON 15 minutes 05/20/2022 01:50 AM ON 15 minutes 05/20/2022 01:55 AM ON 15 minutes This measure was written to get the initial date and time, but it took too long (actually, not loading even for only one day filtered). The final date and time are also a problem. All I need is the duration of each status, but respecting the date and time sequence. If necessary, I can also manipulate this table in Power Query. var status_value = SELECTEDVALUE(table[status]) var date_value = SELECTEDVALUE(table[date and time]) var end_status = CALCULATE(MAX(table[date and time]), FILTER(ALLSELECTED(table[date],table[status]), table[date and time] < date_value && vazoes[status_inicial] <> status_value)) var first_date_status = CALCULATE(MIN(table[date and time]), ALLSELECTED(table[date and time]), table[date and time] > end_status) var first_date = CALCULATE(MIN(table[date and time]),ALLSELECTED(table)) RETURN IF(ISBLANK(end_status),first_date,first_date_status) I attempted to calculate it using DAX in a calculated column, but my dataset with three months of data is no longer loading. So I'm attempting to obtain these values through the use of a measure. Could anyone kindly help me in finding a solution? 🙏 Tks, Matheus
Solved
Anonymous
4 years ago Place DAX Commands and Tips
1.6KViews
0likes
6Comments
Calculate difference between consecutive rows filtered by SELECT DISTINCT
I need to calculate the difference between values in consecutive rows and display the value in the 2nd of each pair of rows, apparently without the help of an index. Specifically, I created a calculated column, Workdays Since Filed, and now want to subtract the Row 1 Workdays value from the Row 2 value and display the result in Row 2 as a calculated measure. Values in red show how it should work: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED (calculated) DAYS BETWEEN (calc) (Just to show math, not a real column) FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 16 - 11 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 17 - 16 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 20 - 17 Getting the "Days Between" calculation is complicated by the filters on this table that made using an index difficult. The main filter is PERMIT_ID and that could be indexed using dynamic filtering tricks I saw on other posts in this forum, but the filter that really hoses things up is a SELECT DISTINCT that removes duplicate rows from display when multiple new documents are received in the same day. So the user sees this: Behind the scenes, the table actually looks like this: PERMIT_ID ACTION ACTION DATE FILED DATE WORKDAYS SINCE FILED DAYS BETWEEN FFXREC215FN Submission Deficiencies Issued 11/29/2021 11/12/2021 11 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 5 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/6/2021 11/12/2021 16 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 1 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN New Document Received 12/7/2021 11/12/2021 17 0 FFXREC215FN Waiting for Information 12/10/2021 11/12/2021 20 3 I worked through solutions on about 5 similar posts but didn't find one that fit this issue specifically. Any ideas? Thanks, Allison
Solved
Anonymous
4 years ago Place DAX Commands and Tips
4.1KViews
0likes
2Comments