Forum Discussion

prita's avatar
prita
Helper I
4 years ago
Solved

Calculate min date with multiple columns

Hello,

 

I have a data table that looks like follows:

 

User IDProduct IDUsage Date
User112411/02/2019
User222305/18/2020
User389306/29/2019
User112411/03/2019
User112412/15/2019
User112401/03/2021
User445607/01/2021
User322303/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 IDProduct ID First Used
User112411/02/2019
User222305/18/2020
User389306/29/2019
User322303/05/2021
User445607/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 IDProduct IDFirst Used
User445607/01/2021
User322303/05/2021

 

But now it is showing:

 

User IDProduct IDFirst Used
User112401/03/2021
User445607/01/2021
User322303/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. 

  • prita's avatar
    prita
    4 years ago

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

    )

2 Replies

  • 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's avatar
      prita
      Helper I

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

      )