Help with Returning Earliest Date based on certain criteria

[Anonymous]

Hi All, hoping someone might be able to help as I have tried a few different ways to do this but fail each time to get what I need.

So I have one table with a list of Products, and the number of times they are used, also includes the week number and date used.

I need to create a new column or measure that can return the earliest date for each product whose number is 2 but this would need to filter per week.
 For example I would like to return the earliest date that Product 2 was used 2 times but one value would need to be returned for week 10 and another for week 11. see data set below

 I have tried switch function, Minx and Min but nothing returns what I want just wondering what would give me this result.

Table1

Product            No    Week             Data
Product 1        1        10        03/03/2018 12:05
Product 1        1        10        03/03/2018 12:06
Product 1        2        10        03/03/2018 13:05
Product 1        2        10        03/03/2018 13:06
Product 1        2        10        03/03/2018 13:07
Product 2        2        10        04/03/2018 15:07
Product 2        2        10        04/03/2018 15:08
Product 2        2        10        04/03/2018 15:09
Product 2        2        10        04/03/2018 15:10
Product 2        3        10        04/03/2018 17:10
Product 2        3        10        04/03/2018 17:20
Product 3        1        10        05/03/2018 09:20
Product 3        1        10        05/03/2018 09:21
Product 3        1        10        05/03/2018 09:22
Product 3        2        10        05/03/2018 14:20
Product 3        2        10        05/03/2018 14:21
Product 3        2        10        05/03/2018 14:22
Product 2        2        11        11/03/2018 15:07
Product 2        2        11        11/03/2018 15:08
Product 2        2        11        11/03/2018 15:10

Any advice or help that anyone could provide would be great as I think my brain has hit its limit with it:)

[v-piga-msft]

Hi @Anonymous ,

By my test with your data sample, the measure below could achive your desired output.

Measure =
CALCULATE (
    MIN ( 'Table1'[Date] ),
    FILTER (
        ALLEXCEPT ( Table1, 'Table1'[Week], Table1[Product] ),
        'Table1'[Number] = 2
    )
)

Here is the output.

Best  Regards,

Cherry

[TwinJohnson]

Hi @v-piga-msft ,

I have a similar dilemna to this quesiton and trying to incorporate your solution into my calculation and wondering if you can assist.  I have a 'Date Table' with [Date field] and [Week Ending Date] and trying to find the "Earliest" reviewed order date from the 'Data [Reviewed Order Date] that was still open at each 'Date Table' [Week Ending Date]'.  

Thank you so much,

Tawnee

[Anonymous · Accepted solution]

Hi Cherry, that worked beautifully, thank you so much, and thank you for quick response. This forum is such a great resource for newbies to powerbi like myself and I am sure more seasoned users. Thanks again really helpful.