Creating A Calculated Field or Dax Measure for Hire date

[LeeI]

I am struggling to come up with a way to Calculate a hire date with data from a table that I have been giving. Ultimately, I would like to be able to show the number of hires in a particular moment of time in a power BI visual. Additionally, I would like to be able to use the number of hires for a particular date, to show total reduction (if any) for a specific job title. This is what the data currently looks like for one agent, The example data listed below is for one agent. But I have every agent for several different job titles all in one table. 

User ID	Employee Name	Period	Job Title
Agent1ID	Agent1Name	4/1/2024	Agent1JobTitle
Agent1ID	Agent1Name	3/1/2024	Agent1JobTitle
Agent1ID	Agent1Name	2/1/2024	Agent1JobTitle
Agent1ID	Agent1Name	1/1/2024	Agent1JobTitle

What I am hoping to accomplish, is look at each individual agent, and see what their last period is. In the example data above, the last period is 1/1/2024. This means that when the data was pulled in December, the agent did not exsist. Therefore his hire date was sometime in December (I am not looking to get a specific hire date), just to show that his hire month and year  was 12/1/2023. 

I have created several measures that does return the hire date. But when I go to use the measure in a matrix, it does not let me use the measure that I have created as a column or row. Because of this, I think that I either need to do this calculation to achieve the hire date and year in either power query, or in a calculated field. But I am unsure what the best way to do this would be. Ultimately, I believe that something like this would be what I am looking for. 

User ID	Employee Name	Period	Job Title	Hire Date
Agent1ID	Agent1Name	4/1/2024	Agent1JobTitle	12/1/2023
Agent1ID	Agent1Name	3/1/2024	Agent1JobTitle	12/1/2023
Agent1ID	Agent1Name	2/1/2024	Agent1JobTitle	12/1/2023
Agent1ID	Agent1Name	1/1/2024	Agent1JobTitle	12/1/2023

[Joe_Barry]

Hi @LeeI 

First you need to define the first date in the record then minus - 1 month to find the Hire Date.

Hire Date = 
VAR _FirstDate = MIN('Table'[Column1])
RETURN
   EOMONTH(_FirstDate, -2) +1

Joe

Please mark as accepted if it answers your question

[LeeI]

Hey @Joe_Barry , I might not have been clear enough in my description, and I apologize. I have tried something similar to this, but I need to have the calculation look at each agent individually. If I use the DAX you sent over in a calculated column, it just finds the minium date in the period columnn for all agents, and returns the value of the previous month. I have tried adding another variable for agent ID,  but that does not appear to return my expected results either. Below is the dax expression that I have tried and is not working, and a table that shows what I am trying to accomplish. 

I have tried this dax expression to try to do it by agent, but it also does not appear to be working 

HireDateTest =

VAR MINPeriod = MIN(MorMemMonthly[PeriodFormatted])

VAR SelectedEMP = MorMemMonthly[Employee_ID]

RETURN

    Calculate (EOMONTH(MINPeriod,-1),SelectedEMP = MorMemMonthly[Employee_ID])

The Hire Date column is what I am trying to accomplish by creating a calculated field 

Employee ID	EmployeeNAME	Period	Hire Date
Employee1ID	John Doe	4/1/2024	2/1/2024
Employee1ID	John Doe	3/1/2024	2/1/2024
Employee2ID	Sam Smith	4/1/2024	12/1/2023
Employee2ID	Sam Smith	3/1/2024	12/1/2023
Employee2ID	Sam Smith	2/1/2024	12/1/2023
Employee2ID	Sam Smith	1/1/2024	12/1/2023

[Joe_Barry · Accepted solution]

No worries

Lets try a different approach.

Create a custom column in your table

HireDate = 
VAR _FirstRecord = 
CALCULATE (
    MIN ( 'Table'[Column1]),
    FILTER (
        'Table',
        'Table'[Name] = EARLIER ( 'Table'[Name] )
    )
)

RETURN
EOMONTH(_FirstRecord, -2) +1

[LeeI]

Hi @Joe_Barry I believe that this accomplished exactly what I am looking for. Thank you for the prompt support! I do have a question for you though. It was my understanding that the calculate function has filters integrated into the function after the expression. In this particular example, why do you need to use the filter function within the calculate function. I guess what I am really asking, is do you know why the dax expression that I was using previously was not working? 

Anyway, I will mark your answer as the accepted solution. Thanks again!