How to filter a Table Visual by earliest date (multiple conditions & slicer)

[oooxxi123]

Hi everyone -

I am trying to generate a fact table (Visual Table) in PowerBI that captures the record with the earliest date than an employee has changed employers AND this date must be within the range defined by a date slicer (e.g. from 01-Jan-2018 to 30-Jun-2018). My current formula goes something like this:

EarliestChangeDate =
var EarliestChangeDate = MINX(FILTER(Table,Table{EmployeeID] = EARLIER(Table[EmployeeID]) && Table[EmployeeEndDate] >= DATE(2018,01,01) && Table[EmployeeEndDate] <= DATE(2018,06,30),Table[EmployeeEndDate])

RETURN IF (Table[EmployeeEndDate] = EarliestChangeDate,1,0)

• This formula above currently works but is manual, as I have to change the DATE (YYYY,MM,DD) manually to reflect what is selected in the slicer;
• The date slicer is built from the field 'Table[EmployeeEndDate]';
• The final result I want is an indicator column (1,0) that tells me whether the line item in the Table Visual is the earliest record given the date parameter of 01-Jan-2018 to 30-Jun-2018;
• For example, I want the final table to look something like below for 'A100', where the first row will exist in my source data BUT not in the table visual, and only the second and third row will show with an indicator (1,0)

EmployeeID	EmployeeEndDate	EarliestChangeDate
A100	06-Mar-2015
A100	25-Apr-2018	1
A100	14-May-2018	0

I've tried many of the online solutions to make the DATE (YYYY,MM,DD) part dynamic but nothing seems to work, so I'm hoping that someone here can help!

Thanks!

[V-lianl-msft · Accepted solution]

Try to create a measure like below:

Measure = 
VAR EMP_ROW = MAX('Table'[EmployeeID])
VAR MIN_ = CALCULATE(MIN('Table'[EmployeeEndDate]),FILTER(ALLSELECTED('Table'),EMP_ROW='Table'[EmployeeID]))
RETURN IF(MAX('Table'[EmployeeEndDate])=MIN_,1,0)

[amitchandak]

@oooxxi123 , You can not use a slicer value in a calculated column. Seems like you are using a calculated column.

You need to create a measure

example

EarliestChangeDate =
var _max = maxx(allselected(Date), Date[Date])
var EarliestChangeDate = MINX(FILTER(allselected(Table),Table{EmployeeID] = max(Table[EmployeeID]) && Table[EmployeeEndDate] >= _max && Table[EmployeeEndDate] <= _max,Table[EmployeeEndDate])

RETURN IF (max(Table[EmployeeEndDate]) = EarliestChangeDate,1,0)

[oooxxi123]

Thank you @amitchandak for the reply!

I have tried to use the example expression above as a field in my visual table, however it does not load (and when I check the field individually, it does not seem like the expression is working);

• I see that, when defining the '_max' variable, you are using a field called 'Date' - which I'm assuming is from a calendar table of some sorts.
• I currently have a calendar table built but not linked by any relationship to the master table which houses the employee data (end date, ID etc.). 
• My slicer is also built using the field 'EmployeeEndDate' rather than the date value from the calendar table

Would this be causing the issue ? Or have I done something wrong here?

Thank you again.

[amitchandak]

@oooxxi123 , Calculate _max, based on slicer date and try  

[oooxxi123]

Hi @amitchandak ,

I have tried to use the measure above with EmployeeEndDate as the calculating field, but I am still running into issues w/ calculation. When I have the slicer for the date period 30-Jun-2018 to 31-Dec-2018, the following data shows (sample below):

EmployeeID	EmployeeEndDate	Earliest (1 - initial column calc)	Earliest (2 - your measure calc)
A100	7-Aug-2018	1	0
A100	27-Nov-2018	0	0
B500	31-Dec-2018	1	1
C200	31-Dec-2018	1	1

• It seems that the 'Earliest 2' measure only returns value where the end date is exactly equal to the max period defined by my slicer
• Additionally, when I load the 'Earliest 2' measure with additional fields in the table (from other data tables), it seems to take a very long time to load and ultimately does not load
• For reference, this is the expression I have settled on to calculate 'Earliest 2':

Date_EarliestSwitch = 
VAR _MAX = MAXX(ALLSELECTED(Table[EmployeeEndDate]),Table[EmployeeEndDate])
VAR Date_EarliestSwitch = MINX(FILTER(ALLSELECTED(Table),Table[EmployeeID] = MAX(Table[EmployeeID]) && Table[EmployeeEndDate] >= _MAX && Table[EmployeeEndDate] <= _MAX),Table[EmployeeEndDate])

RETURN IF (MAX(Table[EmployeeEndDate]) = Date_EarliestSwitch,1,0)

Would you have any advice on this or be able to guide me in the right direction?

Thank you for the help thus far