Calculate min date with multiple columns

[prita]

Hello,

I have a data table that looks like follows:

User ID	Product ID	Usage Date
User1	124	11/02/2019
User2	223	05/18/2020
User3	893	06/29/2019
User1	124	11/03/2019
User1	124	12/15/2019
User1	124	01/03/2021
User4	456	07/01/2021
User3	223	03/05/2021

Each user has a unique ID to identify them, each product has a unique ID, but the table contains rows for all dates each user has used each product. I am trying to get the value of the first date when a user used a certain product. I have created a table (visual) where I have used the Usage date and summarized it by earliest, for example below.

User ID	Product ID	First Used
User1	124	11/02/2019
User2	223	05/18/2020
User3	893	06/29/2019
User3	223	03/05/2021
User4	456	07/01/2021

I need to add a date filter to the page such that when a date is selected from the filter it shows the correct earliest date value in the Table (visual).

I am using a simple date filter but when I select a year, it is changing the table to adjust to the earliest date in that year, for example, if I select the year 2021, I want to see these rows only

User ID	Product ID	First Used
User4	456	07/01/2021
User3	223	03/05/2021

But now it is showing:

User ID	Product ID	First Used
User1	124	01/03/2021
User4	456	07/01/2021
User3	223	03/05/2021

Earliest date for User1 and Product ID 124 is in 2019, so it should show in 2019 only. The row should not appear when year 2021 is selected from date filter. 

[amitchandak]

@prita , create a measure like

and use in place of date

measure =
var _min = calculate(min(Table[Date]), allexcept(Table, Table[User Id],Table[Product ID]))
return
calculate(min(Table[Date]), filter(table, year(_min) = selectedvalue(table[date])))

[prita · Accepted solution]

Thanks! I used the following column and it seems to work for me:

Min date =

CALCULATE (

MIN ( table[USAGE_DATE] ),

ALLEXCEPT ( table, table[userID], table[PRODUCTID] )

)