cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

## 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.

1 ACCEPTED SOLUTION
Community Support

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 5
Helper III

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.

Helper III

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!

Community Support

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.

Solution Specialist

Hey @awff. This should do the trick:

``````New Table =
SUMMARIZE( 'Table', 'Table'[Client], 'Table'[Product] ),
"Earliest Date", CALCULATE( MIN( 'Table'[Start Date] ) )
)``````
Helper III

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?

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors