How to attach previous months run date to current month

[LeeI]

I have a table that I am using that that contains all employees for a certain business segment that is appended each month with the current months information. I am trying to use this data to determine when approximately an employee was hired, and arrive at the month and year of the hire date. I have been thinking the best way for me to do this is see if an employee has a record for the previous month, and if not, then the hire date would be the current month. We also have people that leave, and then come back, so if their last "as of" date was less than the previous month, I also want to mark them as a new hire. Here is what the table currently looks like 

Employee ID	Full Name	Position	As of
ABCD1234	Employee Name 1	Position name	5/1/2024
ABCD2345	Employee Name 2	Position name	5/1/2024
ABCD3456	Employee Name 3	Position name	5/1/2024
ABCD1234	Employee Name 1	Position name	4/1/2024
ABCD2345	Employee Name 2	Position name	4/1/2024
ABCD4567	Employee Name 4	Position name	4/1/2024
ABCD3456	Employee Name 3	Position name	3/1/2024
ABCD1234	Employee Name 1	Position name	3/1/2024
ABCD2345	Employee Name 2	Position name	3/1/2024
ABCD3456	Employee Name 3	Position name	3/1/2024

So in this example, I would like to either use a calculated column or measure, to retrun the previous months as of date for each specific agent, for each instance. (I am relatively new to power BI, and I think a measure would be best if possible so that the calculation is not stored. But I am not sure that what I am looking to do can be accomplished with a measure because of the lack of row syntax).For example I am hoping to get something similar to the below. I could then use see when previous as of date is Null ( like for Agent ABCD4567 ) to determine agent ABCD4567 was hired sometime in March. I could then also could also count agent ABCD3456 as a new hire for May 2024, because they did not have a record for April. 

Employee ID	Full Name	Position	As of	Previous As Of
ABCD1234	Employee Name 1	Position name	5/1/2024	4/1/2024
ABCD2345	Employee Name 2	Position name	5/1/2024	4/1/2024
ABCD3456	Employee Name 3	Position name	5/1/2024	3/1/2024
ABCD1234	Employee Name 1	Position name	4/1/2024	3/1/2024
ABCD2345	Employee Name 2	Position name	4/1/2024	3/1/2024
ABCD4567	Employee Name 4	Position name	4/1/2024	NULL
ABCD3456	Employee Name 3	Position name	3/1/2024	2/1/2024
ABCD1234	Employee Name 1	Position name	3/1/2024	2/1/2024
ABCD2345	Employee Name 2	Position name	3/1/2024	2/1/2024
ABCD3456	Employee Name 3	Position name	3/1/2024	2/1/2024

[mark_endicott · Accepted solution]

@LeeI - Please try this code, it is working in my file using your test data:

VAR selected_emp =
    SELECTEDVALUE ( 'Table (8)'[Employee ID ] )
VAR current_date =
    SELECTEDVALUE ( 'Table (8)'[As of ] )
VAR _table =
    CALCULATETABLE (
        ADDCOLUMNS ( 'Table (8)', "max_date", MAX ( 'Table (8)'[As of ] ) ),
        'Table (8)'[Employee ID ] = selected_emp,
        'Table (8)'[As of ] < current_date
    )
VAR calc =
    MAXX ( _table, [max_date] )
RETURN
    calc

If this works for you, please mark it as the solution.

[LeeI]

Hey Mark, 

I appreciate the prompt response, and yes this appears to almost be working. However, I am seeing some issues when I implement the measure code listed above. Here is an example of one of the errors that I am seeing. I have an agent that has a record for June 2023, and July 2023. However, the June 2023 as of date is not being grabbed as the "previous as of date" for the month of July. Here is an example of what the table looks like 

Emp_ID	Full Name	Position	As Of	Previous Month as Of
ABCD1234	Employee 1	Job title	Sunday October 1 2023	8/31/2023
ABCD1234	Employee 1	job title	Thursday August 31, 2023	7/31/2023
ABCD1234	Employee 1	job title	Monday, July 31, 2023
ABCD1234	Employee 1	job title	Friday, June 30, 2023	5/31/2023

Row three should have a previous month as of date of 6/30/2023. But for some reason, it does not appear to be picking up that value. Any Ideas as to why that might be? 

[LeeI]

I have since learned that this is due to a data integrity issue. I am going to mark your response as a accepted solution for this particular question, because it is functioning as it should! 

[mark_endicott]

@LeeI - Great to hear, happy I could help!

[mark_endicott]

@LeeI - I should also have said, please make sure you select "Show items with no data" on the table, click on Employee ID to show this option. See below: