Calculate min date of the group where this group is 100% done progressed

[Jamie_wong]

I have a table with 3 columns (RecordDate, ProjectID, Progress%). It is possible that
(1) the progress% of the projectID is same for coming days later
(2) the progress% of the projectID has not reach 100%

RecordDate    ProjectID   Progress%

2019-11-01    123            15
2019-11-03    123            50
2019-11-05    123            84
2019-11-07    123            100

2019-11-09    123            100
2019-11-05    456            50
2019-11-09    456            84
2019-11-10    456            100

2019-11-12    456            100
2019-11-05    789            50
2019-11-07    789            75

I wish to have a calculated column that show the min RecordDate where the Progress% is 100% from that group of ProjectID

RecordDate    ProjectID   Progress%

2019-11-07    123            100

2019-11-10    456            100

[v-jayw-msft]

Hi @Jamie_wong,

Please check following calculated column as below and see if the result achieve your expectation:

Column =

VAR md100 =

    CALCULATE (

        MIN ( 'Table'[RecordDate] ),

        FILTER (

            'Table',

            'Table'[ProjectID] = EARLIER ( 'Table'[ProjectID] )

                && 'Table'[Progress%] = 1

        )

    )

RETURN

    IF ( 'Table'[RecordDate] = md100, md100, BLANK () )

Result would be shown as below:

BTW, Pbix as attached, hopefully works for you.

Best Regards,

Jay

Community Support Team _ Jay Wang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.