cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jamie_wong
Frequent Visitor

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

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

 

 

1 REPLY 1
v-jayw-msft
Community Support
Community Support

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:

1.PNG

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.

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors