Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

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"

ToolDate OutSequenceReasonPrevious Reason
T130-Oct-211Well Total Depth 
T124-Nov-211Penetration Rate 
T126-Nov-212Downhole Tool Failure Penetration Rate
T108-Oct-211Hole Problems 
T1012-Oct-212Well Total DepthHole Problems
T1022-Oct-211Downhole Tool Failure  
T1030-Oct-212Pump PressureDownhole Tool Failure 
T107-Nov-213Downhole Steerable Tool FailurePump Pressure
T1014-Nov-211Section Total Depth 
T1015-Nov-212Section Total DepthSection Total Depth
T1016-Nov-213Section Total DepthSection Total Depth
T1019-Nov-214Penetration RateSection Total Depth
T1020-Nov-215Section Total DepthPenetration Rate
T1025-Nov-216Well Total DepthSection Total Depth
T102-Dec-217Well Total DepthWell Total Depth
T1011-Dec-218Hours on BitWell Total Depth
T1118-Oct-211Section Total Depth 
T1119-Oct-212Section Total DepthSection Total Depth
T1121-Oct-213Section Total DepthSection Total Depth
T118-Nov-214Well Total DepthSection Total Depth

 

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
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]
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.

View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
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]
    )

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

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??

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. 


If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Click here to visit my LinkedIn page

Click here to schedule a short Teams meeting to discuss your question.
Anonymous
Not applicable

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

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.