First Date in Group

[zrichardson]

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:

BranchID	FileStartedDate
1	1/1/2017
1	1/3/2017
1	12/14/2016
2	11/30/2016
2	1/31/2017
3	3/1/2017
3	1/1/2017
3	10/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?

BranchID	FileStartedDate	BranchStartDate
1	1/1/2017	12/14/2016
1	1/3/2017	12/14/2016
1	12/14/2016	12/14/2016
2	11/30/2016	11/30/2016
2	1/31/2017	11/30/2016
3	3/1/2017	10/1/2016
3	1/1/2017	10/1/2016
3	10/1/2016	10/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 · Accepted solution]

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]

Could this work?

BranchStartDate = Calculate(
               FIRSTDATE('Table'[FileStartedDate]), 
                
                Filter(all('Table'), 'Table'[BranchID]=EARLIER('Table'[BranchID])))

[zrichardson]

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

[Phil_Seamark]

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.

🙂

[zrichardson]

The Table 'Loans' contains the Branch ID, and 'MilestoneDates' contains Started Date.

Started Date is also linked to a calendar table.

[Phil_Seamark]

Have you considered creating a new table whcih is a merge of the two tables?  Then create your measure on that?

This can be done in DAX but might be more efficient to do upstream.

[zrichardson]

The two tables are 'Loans' which contains the Branch ID, and MilestoneDates which has the started date. They are Linked with the relationship below.

 Started Date is also inactively linked to a calendar table.