Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Help with Returning Earliest Date based on certain criteria

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:)

Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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.

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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.

    • TwinJohnson's avatar
      TwinJohnson
      Frequent Visitor
      3 years ago

      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