Reply
vincenardo
Helper I
Helper I
Partially syndicated - Outbound

Find Earliest Date on Filtered Set of Values in Table

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 -

 

2022-05-22_6-00-49.png

 

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!

1 ACCEPTED SOLUTION
ryan_mayu
Super User
Super User

Syndicated - Outbound

@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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
ryan_mayu
Super User
Super User

Syndicated - Outbound

@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])

1.PNG





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Syndicated - Outbound

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!

Syndicated - Outbound

@vincenardo 

you can try this

 

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

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




hnguy71
Super User
Super User

Syndicated - Outbound

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. 



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!
avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

FebPBI_Carousel

Power BI Monthly Update - February 2025

Check out the February 2025 Power BI update to learn about new features.

March2025 Carousel

Fabric Community Update - March 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)