Find Earliest Date on Filtered Set of Values in Table

[vincenardo]

I need to find the earliest date in my data, for Each Job No and PhaseID but exclude from my result any dates that are 12/30/1899.

 Here is a sample of my data -

I have tried various solutions, but none seam to provide the result. I was able to get the result be creating a new column called 'SchedStartDateFiltered' which uses a conditional statement to return the ScheduledStartDate or blank if = 1899 and then use a measure formula, but I need to create this as a column with a DAX statement so I can use it in my matrix table.

Help is appreciated!

[ryan_mayu · Accepted solution]

@vincenardo 

you can also try this

Column = MINX(FILTER('Table','Table'[schedulestartdate]<>date(1899,12,30) && 'Table'[jobno]=EARLIER('Table'[jobno])&&'Table'[phaseid]=EARLIER('Table'[phaseid])),'Table'[schedulestartdate])

[vincenardo]

If I want to do the same but only filter on Job No and not inclde Phase ID, can you provide me the adjustment? The solution you provided works perfectly, I just want the option. Thanks!

[ryan_mayu]

@vincenardo 

you can try this

Column = MINX(FILTER('Table','Table'[schedulestartdate]<>date(1899,12,30) && 'Table'[jobno]=EARLIER('Table'[jobno])),'Table'[schedulestartdate])

[hnguy71]

Hi @vincenardo ,

Please try this as a measure:

MinDate = CALCULATE(MIN(YOUR_TABLE[ScheduleStartDate]), ALLEXCEPT(YOUR_TABLE,YOUR_TABLE[Job No]),YOUR_TABLE[ScheduleStartDate] <> DATE(1899, 12, 30))

Alternatively, you can do the same in a calculated column:

MinDate = CALCULATE(MIN(YOUR_TABLE[ScheduleStartDate]), FILTER(YOUR_TABLE, YOUR_TABLE[Job No] = EARLIER(YOUR_TABLE[Job No]) && YOUR_TABLE[ScheduleStartDate] <> DATE(1899, 12, 30)))

Let me know if that works out for you.