Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
LeeI
Frequent Visitor

How to attach previous months run date to current month

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 1Position name 5/1/2024
ABCD2345Employee Name 2 Position name5/1/2024
ABCD3456Employee Name 3 Position name5/1/2024
ABCD1234

Employee Name 1

Position name4/1/2024
ABCD2345Employee Name 2 Position name4/1/2024
ABCD4567Employee Name 4 Position name4/1/2024
ABCD3456Employee Name 3 Position name3/1/2024 
ABCD1234Employee Name 1 Position name 3/1/2024
ABCD2345Employee Name 2 Position name 3/1/2024
ABCD3456Employee 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 1Position name 5/1/20244/1/2024 
ABCD2345Employee Name 2 Position name5/1/20244/1/2024
ABCD3456Employee Name 3 Position name5/1/20243/1/2024
ABCD1234

Employee Name 1

Position name4/1/20243/1/2024
ABCD2345Employee Name 2 Position name4/1/20243/1/2024
ABCD4567Employee Name 4 Position name4/1/2024NULL 
ABCD3456Employee Name 3 Position name3/1/2024 

2/1/2024

ABCD1234Employee Name 1 Position name 3/1/20242/1/2024
ABCD2345Employee Name 2 Position name 3/1/20242/1/2024
ABCD3456Employee Name 3 Position name 3/1/2024

2/1/2024 

 

1 ACCEPTED SOLUTION
mark_endicott
Solution Specialist
Solution Specialist

@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.

View solution in original post

5 REPLIES 5
mark_endicott
Solution Specialist
Solution Specialist

@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.

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_IDFull Name Position As Of Previous Month as Of 
ABCD1234 Employee 1 Job title Sunday October 1 20238/31/2023
ABCD1234Employee 1 job title Thursday August 31, 2023 7/31/2023
ABCD1234Employee 1 job title Monday, July 31, 2023  
ABCD1234Employee 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
Frequent Visitor

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! 

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

@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:

 

mark_endicott_0-1716549559302.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.