Forum Discussion
Finding the earliest date across multiple rows
Hi fellow PBI'ers,
I'm trying to create a measure that lists the earliest date a product was held by customers.
Below is a simplified mock up data with the earliest dates bolded:
| Client | Product | Start Date | End Date |
| A | 1 | 1-Jan-21 | 1-Jan-22 |
| A | 1 | 1-Jun-10 | 1-May-10 |
| B | 2 | 1-Dec-21 | 1-Dec-22 |
| B | 3 | 1-Apr-21 | 1-Apr-22 |
| B | 2 | 1-May-19 | 1-May-20 |
| C | 3 | 1-Feb-21 | 1-Feb-22 |
| C | 3 | 1-Mar-21 | 1-Mar-22 |
The result ideally should list the earliest date possible per customer by product:
| Client | Product | Earliest Date |
| A | 1 | 1-Jun-10 |
| B | 2 | 1-May-19 |
| B | 3 | 1-Apr-21 |
| C | 3 | 1-Feb-21 |
I'm assuming it will be a combination of SUMMARIZE and ALL however, for some reason my results are either:
* Earliest date of the most recent row or;
* Earliest date for each product but across ALL clients rather than per client and product.
Hi awff ,
Try this measure to see if this will meet your needs:
Measure = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Client],'Table'[Product]))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
I played around with the syntax and this seems to work as a measure:
First Start Date = Calculate( FIRSTDATE(table[Start Date]), Filter( ALLEXCEPT(table, table[Account], table[Product]), table[Start Date] <= Min(table[Start Date])))Cross checking with a small sample of the data seemed to return the correct values so far.
Ok, So this only seems to work when drilled down clicking on a row in the table.
But when not filtered, It seems to returning the earliest date of the entire data set for the particular product!
Hi awff ,
Try this measure to see if this will meet your needs:
Measure = CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Client],'Table'[Product]))Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey awff. This should do the trick:
New Table = ADDCOLUMNS( SUMMARIZE( 'Table', 'Table'[Client], 'Table'[Product] ), "Earliest Date", CALCULATE( MIN( 'Table'[Start Date] ) ) )Thanks SteveHailey ,
I'm assuming this is for a new table
is there a way i can have this as a measure for a table visualisation?
