Forum Discussion

zrichardson's avatar
zrichardson
Advocate III
9 years ago
Solved

First Date in Group

I have a table with branches and File Start Dates. We don't currently have a branch started date in our database so I was going to use the earliest file start date as the branch start date. This would give us a table like so:

 

BranchIDFileStartedDate
11/1/2017
11/3/2017
112/14/2016
211/30/2016
21/31/2017
33/1/2017
31/1/2017
310/1/2016

 

I want to add a Calculated Column:

BranchStartDate = Calculate(Min(Table[FileStartedDate]), Filter(Table, Table[BranchID]=Earlier([BranchID])))

 

This gives me a circular reference error. Is there any formula I can use in a calculated column that would give me this result?

 

BranchIDFileStartedDateBranchStartDate
11/1/201712/14/2016
11/3/201712/14/2016
112/14/201612/14/2016
211/30/201611/30/2016
21/31/201711/30/2016
33/1/201710/1/2016
31/1/201710/1/2016
310/1/201610/1/2016

 

I should probably say that my File Started Date and Branch ID are on 2 different tables with a 1-1 relationship and cross-directional filtering.

 

Thank you!

Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi zrichardson,

     

    To resolve your issue, you should combine the column [BranchID] and [FileStartedDate] into a single table, as is shown in your original post. If you have had a table contains column [BranchID], please create a calculated column like:

    FileStartDate = RELATED(MilestoneDates[FileStartDate])

    Then, you can use below DAX formula to display the earliest file start date.

    BranchStartDate = Calculate(Min(Table[FileStartedDate]), Filter(Table, Table[BranchID]=Earlier([BranchID])))

    Best regards,
    Yuliana Gu

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi zrichardson,

     

    To resolve your issue, you should combine the column [BranchID] and [FileStartedDate] into a single table, as is shown in your original post. If you have had a table contains column [BranchID], please create a calculated column like:

    FileStartDate = RELATED(MilestoneDates[FileStartDate])

    Then, you can use below DAX formula to display the earliest file start date.

    BranchStartDate = Calculate(Min(Table[FileStartedDate]), Filter(Table, Table[BranchID]=Earlier([BranchID])))

    Best regards,
    Yuliana Gu

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    9 years ago

    Could this work?

     

    BranchStartDate = Calculate(
               FIRSTDATE('Table'[FileStartedDate]), 
                
                Filter(all('Table'), 'Table'[BranchID]=EARLIER('Table'[BranchID])))
    • zrichardson's avatar
      zrichardson
      Advocate III
      9 years ago

      I tried that, but I am still getting a circular dependency on the file start date.

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        9 years ago

        Hi there, 

         

        What are your two tables called and can you please post a small sample set of data showing the data as it is in the two columns.

         

        We can definitely join them up for you and produce the results you need.  It's just helpful to see the tables & data as close to your actual structure.

         

        :)