Min Value across Multiple Columns, based on Associated Columns

[pxp920]

Setup:

I have a situation where coaches manually populate check-ins and progressions against milestones for specific members.  These are collected across multiple tabs (one for or each coach).  The tabs look like this:

Important considerations:

• Members do not progress through milestones lineary; they can regress (e.g. see Member "BR") or advance slower than 1 milestone per week (e.g. see Member "AC")
• There is only 1 row per member, hence the population of this wide rather than long ( I don't like it either).

Requirement

identify the earliest date in which a milestone was achieved (and latest, but this should be easy if i know how to do the earliest)

I was thinking something along the lines of finding identifying all the columns with said milestone and then getting the max value of the positionally offset date columns.  eg:

Earliest Milestone 3 Date for Member AC:

• Columns 5, 7, and 9 (columns with Milestone 3) ->
• Max across offset -1 (columns 4,6, and 😎  ->
• 11/5

Not sure how to do this, or if it possible as suggested.  Will take any help here!!

[Anonymous · Accepted solution]

@pxp920 

You first need to make the data table looks like the following, you may check the steps in power query, just some duplicates and remove columns.

Then you should able to find the earliest date with a a measure, also change min to max to get the latest date.

Earliest = CALCULATE(MIN([Message Sent]),FILTER('Union Table',[Member]="AC"&&[Milestone]="Milestone 3" ))

Latest = CALCULATE(MAX([Message Sent]),FILTER('Union Table',[Member]="AC"&&[Milestone]="Milestone 3" ))

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

[pxp920]

Thank you! I ended up restructuring the data from Wide -> Long via union, and then performing the calculation on the long table which made much more sense!

[pxp920]

Important to note - I have already combined all the data from all the coaches in a unified dataset (via union) on powerbi.