Find latest date of a specified range of dates based on multiple conditions

[BalazsNy]

Hi everybody!

I have 2 tables with a many-to-one relationship based on Product ID.

First table is a history table, which contains several steps with time stamps for each product ID. Some of the steps may occur several times.

Second table contains product details, where Product ID is unique.
(Product details table contains around 3000 products/rows | History table contains almost all of the products, with lot of steps, so it has around 50.000 rows)

Small section of tables:

I would like to add a new column to Product details table (column name would be: Last req.def.date before 1st submission), where I would like to get the latest Requirements definition date before the first Offer submission date from History table. Not all the products reached Requirements definition step, these should be blank.

Could you suggest a DAX formula to solve this?

Thanks in advance.

[Ashish_Mathur]

Hi,

Share both tables in a format that can be pasted in an MS Excel file.

[Nilupul]

Try with this :

Last req.def.date = 
VAR _FirstOfferSubDate =
    CALCULATE (
        MIN ( 'I3_History Table'[Time Stamp] ),
        'I3_History Table'[Step] = "Offer submission"
    )
VAR _LatestReqDefDate =
    CALCULATE (
        MAX ( 'I3_History Table'[Time Stamp] ),
        FILTER (
            'I3_History Table',
            'I3_History Table'[Step] = "Requirements definition"
                && 'I3_History Table'[Time Stamp] <= _FirstOfferSubDate
        )
    )
RETURN
    _LatestReqDefDate

[BalazsNy]

Dear Nilupul,

Thanks for the tipp, but unfortunately it doesn't work 100% well for me. It looks like if the formula would forget the Product ID context and generates the latest date of requirements definition of all the products before 1st Offer submission of the selected product (IP-2302). So, the aim would to limit the search of the latest date of requirements definition just to the selected Product ID & not taking into account the rest of the products.

[v-cgao-msft · Accepted solution]

Hi @BalazsNy ,

//I have 2 tables with a many-to-one relationship based on Product ID.
If there is this relationship, Nilupul's formula should work, is the relationship inactive?
By the way, please try this formula:

Last req.def.date before 1st submission = 
VAR _FirstOfferSubDate =
    CALCULATE (
        MIN ( 'History table'[Time Stamp] ),
        'History table'[Step] = "Offer submission"
            && 'History table'[Product ID] = EARLIER ( 'Product details table'[Product ID] )
    )
VAR _LatestReqDefDate =
    CALCULATE (
        MAX ( 'History table'[Time Stamp] ),
        'History table'[Step] = "Requirements definition"
            && 'History table'[Time Stamp] <= _FirstOfferSubDate
            && 'History table'[Product ID] = EARLIER ( 'Product details table'[Product ID] )
    )
RETURN
    _LatestReqDefDate

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