Stumped, cannot get min date

Hi,

Please share data in a format that can be pasted in an MS Excel file.  Also, clearly show the expected result.

Hi jjustjjo​,
Can you try below steps:

1. Create the minimum date measure
If STUDY_SEQ identifies the study/group for which you want the earliest completed date, create this measure:

Min Study Level Completed Date = CALCULATE ( MIN ( 'Milestones Info'[StudyLevel Completed Date] ), ALLEXCEPT ( 'Milestones Info', 'Milestones Info'[STUDY_SEQ] ) )

This is a measure, not a calculated column.

ALLEXCEPT removes the filters coming from the other columns in Milestones Info, including the current row's completed date, while keeping the current STUDY_SEQ filter.

So for every row belonging to the same STUDY_SEQ, you should now get the same earliest date.

2. If you only want the year

Once the minimum date is working, you can return only the year:

Min Study Level Completed Year = VAR MinCompletedDate = CALCULATE ( MIN ( 'Milestones Info'[StudyLevel Completed Date] ), ALLEXCEPT ( 'Milestones Info', 'Milestones Info'[STUDY_SEQ] ) ) RETURN YEAR ( MinCompletedDate )

Best Regards,
Maruthi
LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/
X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

Hi jjustjjo​ 

It’s not impossible, it’s filter context. Because the date columns are in your table visual, each row filters the measure down to that single date, so MIN just returns the current row. FILTER(‘Milestones Info’, ...) doesn’t help either, since it iterates the table after that filter is applied.

Remove the row-level filters and keep only the study:

Min StLevCompYear = VAR _min = CALCULATE( MIN('Milestones Info'[StudyLevel Completed Date]), ALLEXCEPT('Milestones Info', 'Milestones Info'[study_seq]) ) RETURN YEAR(_min)

This returns 2023 on every row for study 52.

Note: ALLEXCEPT also ignores slicers on other columns of that table. If you have those, use REMOVEFILTERS on just the columns in your visual instead:

CALCULATE( MIN('Milestones Info'[StudyLevel Completed Date]), REMOVEFILTERS('Milestones Info'[StudyLevel Completed Date], 'Milestones Info'[SiteLevel Completed Date], 'Milestones Info'[Milestone]), 'Milestones Info'[Milestone level] = "Study" )

Same idea fixes your line chart: if the axis uses a date table, add it to REMOVEFILTERS inside the measures that feed the DATEDIFF.
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

Thank you both maruthisp​  and Ritaf1983​ 
I am using a measure.
Update 3:45 PM EDT: I found a misplaced parentheses.  Now that I have that correction, , to wit:

var vIAlsoDontWork = CALCULATE(min('Milestones Info'[StudyLevel Completed Date]), ALLEXCEPT('Milestones Info','Milestones Info'[STUDY_SEQ]))

All rows return 01-Jan-1990, even though the earliest date for my study_seq is 19-Apr-2023.

 I did just realize that my made-up sample does leave out a layer.  The sample is more like this:

So you have the added complexity multiple site names.  But I don't think this would affect the method of getting the Min date out of StudyLevel Comp Date.