Forum Discussion

a68tbird's avatar
a68tbird
Resolver II
8 years ago

Finding Earliest Date Among Common Entries

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 Stage2016-06-01
Campaign A Jun 06 - Gramercy Theatre2016-06-01
Campaign A Jun 08 - Metro2016-06-02
Campaign A Jun 21 - Belasco Theater2016-07-01
Campaign A Jun 09 - Neumos2016-07-01
Campaign B Jan 12 - Wiltern Theatre2016-04-19
Campaign B Mar 10 - Freedom Hall Civic Center2016-04-19
Campaign B Mar 11 - UTC McKenzie Arena2016-04-19
Campaign B Mar 17 - Berglund Center2016-04-19
Campaign C Oct 11 - Tuscaloosa Amphitheater2017-11-11
Campaign C Apr 20 - Whitewater Amphitheatre2017-11-12
Campaign C Apr 21 - Whitewater Amphitheatre2017-11-12
Campaign C Apr 25 - Humphrey's2017-11-12
Campaign C Apr 27 - Harrah's Laughlin2017-11-12
Campaign C Apr 29 - Santa Barbara Bowl2017-11-12
Campaign C Aug 11 - Edgefield2017-11-12
Campaign C Aug 13 - USANA Amphitheatre2017-11-12
Campaign C Aug 14 - Mountain Winery2017-11-12
Campaign C Aug 17 - Shrine Auditorium2017-11-12
Campaign C Aug 18 - Greek Theatre2017-11-12
Campaign C Jul 11 - Rose Music Center2017-11-12
Campaign C Jul 12 - PNC Pavilion at Riverbend2017-11-12
Campaign C Jun 05 - Ford Center2017-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!

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    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's avatar
      a68tbird
      Resolver II
      8 years ago

      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's avatar
        Ashish_Mathur
        Super User
        8 years ago

        Hi,

         

        Try this calculated column formula

         

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

         

        Hope this helps.