Forum Discussion

zrichardson's avatar
zrichardson
Icon for Advocate III rankAdvocate 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!

  • 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