Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 S...
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    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's avatar
Anonymous
Not applicable
4 years ago

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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
4 years ago

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.