Finding Earliest Date Among Common Entries

[a68tbird]

Hello All,

  I have a situation where I would like to find the earliest date related to a group of entries.  Here's the setup:

Campaigns[CampaignName]	Blocks[DateVenue]	Blocks[AvailabilityEndUtc]
Campaign A	Jun 05 - The Loft at Center Stage	2016-06-01
Campaign A	Jun 06 - Gramercy Theatre	2016-06-01
Campaign A	Jun 08 - Metro	2016-06-02
Campaign A	Jun 21 - Belasco Theater	2016-07-01
Campaign A	Jun 09 - Neumos	2016-07-01
Campaign B	Jan 12 - Wiltern Theatre	2016-04-19
Campaign B	Mar 10 - Freedom Hall Civic Center	2016-04-19
Campaign B	Mar 11 - UTC McKenzie Arena	2016-04-19
Campaign B	Mar 17 - Berglund Center	2016-04-19
Campaign C	Oct 11 - Tuscaloosa Amphitheater	2017-11-11
Campaign C	Apr 20 - Whitewater Amphitheatre	2017-11-12
Campaign C	Apr 21 - Whitewater Amphitheatre	2017-11-12
Campaign C	Apr 25 - Humphrey's	2017-11-12
Campaign C	Apr 27 - Harrah's Laughlin	2017-11-12
Campaign C	Apr 29 - Santa Barbara Bowl	2017-11-12
Campaign C	Aug 11 - Edgefield	2017-11-12
Campaign C	Aug 13 - USANA Amphitheatre	2017-11-12
Campaign C	Aug 14 - Mountain Winery	2017-11-12
Campaign C	Aug 17 - Shrine Auditorium	2017-11-12
Campaign C	Aug 18 - Greek Theatre	2017-11-12
Campaign C	Jul 11 - Rose Music Center	2017-11-12
Campaign C	Jul 12 - PNC Pavilion at Riverbend	2017-11-12
Campaign C	Jun 05 - Ford Center	2017-11-12

The Campaigns table is related to the Blocks Table (c.ID = b.ParentID). What I'd like to determine is the earliest Blocks[AvailabilityEndUtc] among the common campaign names. So, with Campaign A, June 1 is the earliest listed date; Campaign B, the end dates are all the same, so April 19, and with Campaign C, November 11 is the earliest. 

Thanks for your help!

[Ashish_Mathur]

Hi,

Your data has not appear properly.  One cannot paste it into an Excel spreadsheet.  Paste the data such that it can easily be pasted in MS Excel.

[a68tbird]

@Ashish_Mathur I have reformated the data and should be easily pasted into Excel.

@Anonymous Thanks for the suggestion, but not working for me. Please note that I have two joined tables here: Campaigns and Blocks.  If I try that formula you suggested, the intellisense doesn't give me the choices of columns to make it work.  Any suggestions on how to amend that formula to take this into account?

[Ashish_Mathur]

Hi,

Try this calculated column formula

=CALCULATE(MIN(Data[Blocks[AvailabilityEndUtc]]]),FILTER(Data,Data[Campaigns[CampaignName]]]=EARLIER(Data[Campaigns[CampaignName]]])))

Hope this helps.

[Anonymous]

Create a column with this formula:

Earliest Article = if(CALCULATE(MIN(Table1[Date]),FILTER(Table1,Table1[Article]= EARLIER(Table1[Article]))) = Table1[Date],"EarliestCapaign")

Thanks
Raj