Forum Discussion
Return Earlier or previous Date row data based two conditions - sequence and Tool number
I have a Dataset for an Item-"Tool" which is used more than one time , Everytime it is used there is an increamental "Sequence" recorded and the end "Date Out" with reason . It returns to Base for Service and next use the Sequence is Reset to '1"
The Objective is to add a column " Previous Reason" where it will copy the reason of the previous sequence use. If the "Sequence" is 1 then it will be blank .
Create a Measure called " Previous Reason"
For Every "Tool ",If the "Sequence" is >1 , Look for the same Tool #, current Sequence minus 1 ,earlier date , match found then copy the Value of the "Reason" of that Row to " Previous Reason"
I am not able to create measure with 2 Conditions ... Can you help please.
The Final Table in PBI should look like this with new Measure Column " Previous Reason"
| Tool | Date Out | Sequence | Reason | Previous Reason |
| T1 | 30-Oct-21 | 1 | Well Total Depth | |
| T1 | 24-Nov-21 | 1 | Penetration Rate | |
| T1 | 26-Nov-21 | 2 | Downhole Tool Failure | Penetration Rate |
| T10 | 8-Oct-21 | 1 | Hole Problems | |
| T10 | 12-Oct-21 | 2 | Well Total Depth | Hole Problems |
| T10 | 22-Oct-21 | 1 | Downhole Tool Failure | |
| T10 | 30-Oct-21 | 2 | Pump Pressure | Downhole Tool Failure |
| T10 | 7-Nov-21 | 3 | Downhole Steerable Tool Failure | Pump Pressure |
| T10 | 14-Nov-21 | 1 | Section Total Depth | |
| T10 | 15-Nov-21 | 2 | Section Total Depth | Section Total Depth |
| T10 | 16-Nov-21 | 3 | Section Total Depth | Section Total Depth |
| T10 | 19-Nov-21 | 4 | Penetration Rate | Section Total Depth |
| T10 | 20-Nov-21 | 5 | Section Total Depth | Penetration Rate |
| T10 | 25-Nov-21 | 6 | Well Total Depth | Section Total Depth |
| T10 | 2-Dec-21 | 7 | Well Total Depth | Well Total Depth |
| T10 | 11-Dec-21 | 8 | Hours on Bit | Well Total Depth |
| T11 | 18-Oct-21 | 1 | Section Total Depth | |
| T11 | 19-Oct-21 | 2 | Section Total Depth | Section Total Depth |
| T11 | 21-Oct-21 | 3 | Section Total Depth | Section Total Depth |
| T11 | 8-Nov-21 | 4 | Well Total Depth | Section Total Depth |
Hi,
Please check the below DAX formula and the attached pbix file.
It is for creating a new column.
Previous reason CC = VAR currenttool = Data[Tool] VAR currentdate = Data[Date Out] VAR previousdate = MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] < currentdate ), Data[Date Out] ) VAR currentsequence = Data[Sequence] VAR previoussequence = MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Sequence] < currentsequence ), Data[Sequence] ) RETURN MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] = previousdate && Data[Sequence] = previoussequence ), Data[Reason] )
4 Replies
- Jihwan_Kim
Super User
Hi,
Please check the below DAX formula and the attached pbix file.
It is for creating a new column.
Previous reason CC = VAR currenttool = Data[Tool] VAR currentdate = Data[Date Out] VAR previousdate = MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] < currentdate ), Data[Date Out] ) VAR currentsequence = Data[Sequence] VAR previoussequence = MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Sequence] < currentsequence ), Data[Sequence] ) RETURN MAXX ( FILTER ( Data, Data[Tool] = currenttool && Data[Date Out] = previousdate && Data[Sequence] = previoussequence ), Data[Reason] )- Jihwan_Kim
Hi,
Thank you for your message.
I do not know how your data model looks like, and please share your sample pbix file. And then I can try to create a more accurate measure that suits your needs.
Thank you.