DAX Date Function from Range

[Darko_Giac]

Hi all,

I'm currently working with a fairly large data set within power pivot (approximately 1 million rows of data), and am trying to find the earliest date that correspond to a range of IDs that repeat, but are scattered throughout.

For example,

1234 February 2, 2012
5555 May 2, 2004
7777 July 2, 2004
7777 May 6, 2002
1234 August 8, 1997
1234 December 12, 2012
5555 December 22, 1999
0000 August 8, 2008
0000 July 7, 2007

I tried the FIRSTDATE and MIN functions, but they didn't seem to work. Essentially, I am looking for a function that will display the earliest start date for 1234, 5555, 7777, etc...I'm fairly certain there is a middle-man function that's escaping me.

Any help would be greatly appreciated!

D.

[Pete_UK]

Is this to go into a separate table, where you have already listed the IDs like 1234, 5555, 7777 etc., or do you want the earliest date to be displayed on the row of first occurrence of each ID?

You can do this with an array formula, along the lines of:

=MIN(IF(conditions,date_range))

where conditions will be something like:

ID_range = ID_cell

This is a bit vague, but if you can tell us which columns you are using then I can flesh it out for you.

Hope this helps.

Pete

[xlnitwit]

Hi,

Are you trying to create a calculated column in your table or do you want the result in a pivot table, in which case a simple MIN measure should suffice?

[Darko_Giac]

Hi there, this actually worked perfectly. Thanks so much!

Is this to go into a separate table, where you have already listed the IDs like 1234, 5555, 7777 etc., or do you want the earliest date to be displayed on the row of first occurrence of each ID?

You can do this with an array formula, along the lines of:

=MIN(IF(conditions,date_range))

where conditions will be something like:

ID_range = ID_cell

This is a bit vague, but if you can tell us which columns you are using then I can flesh it out for you.

Hope this helps.

Pete

[Darko_Giac]

A simple MIN would be okay, which I also tried and worked quite well! Thanks!

Hi,

Are you trying to create a calculated column in your table or do you want the result in a pivot table, in which case a simple MIN measure should suffice?