Date Filtering Issues

[azlocal21]

Hello everyone!

Super new, and mostly self taught in Power Bi Destkop. I have gotten a lot of help building measures from this forum but I have not been able to find the help I need for this issue.

I'm trying to build a matrix that looks like this:

See a sample table that matches this desired visual above to this post to make it easier to help at the bottom of the page.

Here's one of the measures I have tried:

Most Recent Hire Class = CALCULATE ( MAX ( Table1[Start_Date] ) , Table1[Start_Date]<=TOday() )

Also this one (and every variation I can think of):

Most Recent Filter = VAR _MostRecentFilter = MAX('Table1'[Start_Date])

Return

MINX(

Filter(

('Table1'),'Table1'[Start_Date]=MIn('Table1'[Start_Date]) &&

'Table1'[Start_Date]<=Today()

),

'Table1'[Start_Date]

)

The issue I'm having is it's not isolating only the exams needed and only the most recent start date.

I can create the matrix I'm looking for using Filters on the visuals manually but that would mean I'd have to update the Filter every time a new start date occurs. I really want to avoid this as much as possible. I have not been able to find a measure that can pull the data based on the most recent start date and input all the numbers in relation to that start date.

Any help would be greatly appreciated as I've reached my knowledge limitation.

Sample Table:

Site	Start_Date	Exams_Needed	Current_Status	Finish_Date
TX	2/22/2022	B,C	B 1st Attempt	15-Jul
TX	2/22/2022	B,C	B 1st Attempt	15-Jul
AZ	2/22/2022	B,C	B 1st Attempt	15-Jul
TX	2/22/2022	B,C	B 2nd Attempt	15-Jul
NC	2/22/2022	B,C	B 2nd Attempt	15-Jul
NC	2/22/2022	B,C	C 1st Attempt	1-Jun
NC	2/22/2022	B,C	C 1st Attempt	1-Jun
TX	2/22/2022	None	Exams Complete	1-Apr
AZ	3/7/2022	C	C 1st Attempt	10-Jun
AZ	3/21/2022	A,B,C	A 1st Attempt	10-Aug
NC	3/21/2022	A,B,C	A 1st Attempt	10-Aug
NC	3/21/2022	A,B,C	A 1st Attempt	10-Aug
TX	3/21/2022	A,B,C	A 1st Attempt	10-Aug
NC	3/21/2022	A,B,C	A 1st Attempt	10-Aug
NC	3/21/2022	A,B,C	A 1st Attempt	10-Aug
PA	3/21/2022	A,B,C	A 1st Attempt	10-Aug
PA	3/21/2022	A,B,C	A 1st Attempt	10-Aug
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
NC	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
AZ	3/21/2022	B,C	B 1st Attempt	15-Jul
NC	3/21/2022	B,C	B 1st Attempt	15-Jul
NC	3/21/2022	B,C	B 1st Attempt	15-Jul
NC	3/21/2022	B,C	B 1st Attempt	15-Jul
PA	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	B 1st Attempt	15-Jul
NC	3/21/2022	B,C	B 1st Attempt	15-Jul
PA	3/21/2022	B,C	B 1st Attempt	15-Jul
TX	3/21/2022	B,C	C 1st Attempt	15-Jul
AZ	3/21/2022	None	Exams Complete	10-May
TX	3/21/2022	None	Exams Complete	10-May
AZ	4/4/2022	B,C	Prehire
AZ	4/4/2022	B,C	Prehire
AZ	4/4/2022	A,B,C	Prehire
AZ	4/4/2022	A,B,C	Prehire

[amitchandak · Accepted solution]

@azlocal21 , Try a measure like

var _max = VAR _MostRecentFilter = MAXX( Filter( allselected('Table1') , 'Table1'[Start_Date]<=Today()) ,'Table1'[Start_Date])
Return
calculate(count('Table1'[Start_Date]), filter('Table1', 'Table1'[Start_Date] =__max))

[azlocal21]

When I tried that this is the error message it gives me.

The syntax for ')' is incorrect. (DAX(var _max = VAR MostRecentFilter = MAXX( Filter( allselected('Table1') , 'Table1'[Start_Date]<=Today()) ,'Table11'[Start_Date])Returncalculate(count('Table1'[Start_Date]), filter('Table1', 'Table1'[Start_Date] =_max)))).


**Update**

I removed the VAR _MostRecentFilter from the measure and that works great for giving me the number or most recent hires for the most recent start date.

The problem I am having now is that the finish date is pulling the earliest date for every exam path.