Return Earlier or previous Date row data based two conditions - sequence and Tool number

[Anonymous]

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

[Jihwan_Kim · Accepted solution]

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]
    )

[Anonymous]

Hello Jiwan,

Thanks for the Solution , It works , Fantastic ...Just that I have the data in seperate Tables , Like Date is n DIM Date , Tool is in DIM Tool and Rest in FACT table.

The Solution works when all the data is in one Table.

Is it possible to create a Measure instead of Column??

[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. 

[Anonymous]

Hello Jihwan

Please find the link from my Google Drive: kish.pbix

https://drive.google.com/file/d/1mGGl2YSFPrkif8liwJuZ9YSFeU9NlkeR/view?usp=sharing

If it does not work let me know.. Will try another option

I have included both version - 

All data in single table- Which is working

Date is different table with relationship - 

Thank You