MINX with multiple conditions

[Anonymous]

hi,

I have a table that i am trying to get the date from the previous row. To make it work properly I created an index column. This index column is responsible to classify the information, based on the id, event and date1. 

Here's an example:

This is how it should work, but the datefromprevious is returning the same date for both rows.

like this:

I'm trying to add one more condition in this code, but the "Filter" function only allows me to work with 2 conditions.

I tried to use Calculate function, but it seems to have some kind of incompatibility with MINX function and I can't filter either.

this is the code:

NewColumn = MINX(FILTER(Table[ID] = EARLIER(Table[ID]) && [Index] = EARLIER([Index])-1), [Date1])

How can I use multiple filters in this case?

[Ashish_Mathur]

Hi,

If for an ID and event combination, there can never be a duplicate entry in the Date1 column, then the Index column is not required.  Try this calculated column formula

Previous date  = calculate(max(Data[Date1]),filter(Data,Data[id]=earlier(Data[id])&&data[event]=earlier(data[event])&&data[date1]<earlier(data[date1])))

Hope this helps.

[Anonymous · Accepted solution]

Hi @Anonymous ，

Try this calculated column:

DATEFORMPREVIOUS = 
VAR __cur_id = 'Table'[ID]
VAR __cur_event = 'Table'[EVENT]
VAR __cur_index = 'Table'[INDEX]
VAR __result =
    CALCULATE (
        MIN ( 'Table'[DATE1] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[ID] = __cur_id
                && 'Table'[EVENT] = __cur_event
                && 'Table'[INDEX] = __cur_index - 1
        )
    )
RETURN
    __result

Best Regards,
Gao
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

[Anonymous]

Thank you so much! This solved my problem!

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

OFFSET function (DAX) - DAX | Microsoft Learn

Expected result CC = 
MAXX (
    OFFSET (
        -1,
        'Table',
        ORDERBY ( 'Table'[INDEX], ASC ),
        ,
        PARTITIONBY ( 'Table'[EVENT] ),
        MATCHBY ( 'Table'[DATE1], 'Table'[EVENT], 'Table'[ID], 'Table'[INDEX] )
    ),
    'Table'[DATE1]
)

[Anonymous]

Thank you for your help!

I'm trying to apply this solution into the real case, but it seems to not work because the "relation" parameter from offset has duplicated lines. How can I send you the file back with a table that is more like the real case?

[Jihwan_Kim]

Hi,

Thank you for your message, and you can share the link of the file (Onedrive, Dropbox, Googledrive, others...).

Thank you.