cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
zrichardson
Advocate I
Advocate I

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!

1 ACCEPTED SOLUTION
v-yulgu-msft
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
v-yulgu-msft
Microsoft
Microsoft

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Phil_Seamark
Microsoft
Microsoft

Could this work?

 

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

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

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

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.

 

🙂


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

2017-02-22 12_38_45-.jpg

 

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

 

Started Date is also linked to a calendar table.

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.


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

The two tables are 'Loans' which contains the Branch ID, and MilestoneDates which has the started date. They are Linked with the relationship below.2017-02-22 12_38_45-.png

 

 Started Date is also inactively linked to a calendar table.

Helpful resources

Announcements
August 2022 update 768x460.jpg

Check it Out!

Click here to learn more about the August 2022 updates!

August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors