Employee Promotions

[yaman123]

Hi, 

I need help with a query to show employee promotions within a selected month. 

I have a date table which is used as a slice to show data for that month. MAX(date) is used. 

I have a promotions table with NAME, EMP NO, START DATE, END DATE, JOB TITLE, VALID FROM, VALID TO. 

Employees can have more than one line in this table if there has been a change in their job hence the valid from and valid to date fields are populated with the start and end date of that particular job role. 

[Anonymous]

Hi @yaman123 ,

Based on your description, you can do some steps as follows.

1. Create a calculated table.

Table 3 =

SUMMARIZE (

    'Table 2',

    'Table 2'[EMP NO ],

    'Table 2'[NAME],

    "OLD ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) < 3000 ), [JOB TITLE] ),

    "NEW ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) > 3000 ), [JOB TITLE] ),

    "FROM DATE", MAX ( 'Table 2'[VALID FROM] )

)

2. Create a measure.

Measure =

var x1=SELECTEDVALUE('Date'[Date].[Month])

return

IF(MAXX('Table 3',[FROM DATE].[Month])=x1,1,0)

3. Create a table visual and drag the measure to “filters on this visual”.

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Or if i can show the last 6 months of changes instead? 

[yaman123]

Hi @Anonymous 

Thanks for this

But my measure is returning all zeros for all employees. I have a seperate date table not linked to any of the tables im using and i'm using the MonthYear column to select a date period. 

[Anonymous]

Hi @yaman123 ,

Did you get the following table?

Or can you share some screenshots around the issue please? This is not helping much.

Add error screenshot as well which you are facing.

Best Regards,

Yuna

[yaman123]

Hi,

I have created the table with this query

Table 3 =

SUMMARIZE(

'Promotions and Transfers',

'Promotions and Transfers'[EMP_NO],

'Promotions and Transfers'[INTERNAL_DISPLAY_NAME],

"OLD ROLE", MAXX(FILTER('Promotions and Transfers',YEAR('Promotions and Transfers'[VALID_TO]) < 3000),'Promotions and Transfers'[JOB_TITLE]),

"NEW ROLE", MAXX(FILTER('Promotions and Transfers',YEAR('Promotions and Transfers'[VALID_TO]) > 3000), 'Promotions and Transfers'[JOB_TITLE]),

"FROM DATE", MAX('Promotions and Transfers'[VALID_FROM])

)

I have created a measure in the created table:

Measure =

VAR x1 = SELECTEDVALUE('Date'[Date].[Month])

return

IF(MAXX('Table 3','Table 3'[FROM DATE].[Month]) = x1,1,0)

I am getting the attached results when i select Aug 2020. My measure column is always displayed as '0' instead of showing '1' for selected month. All employees are showing instead of the promotions for that month

[Anonymous]

Hi @yaman123 ,

You said that"I have a seperate date table not linked to any of the tables im using and i'm using the MonthYear column to select a date period. " The field I used in the slicer is the "Date" column. 

the attachment file

Best Regards,

Yuna

[yaman123]

So would i need to use the date column instead of the month year column from the date table? 

[Anonymous]

Hi @yaman123 ,

You can try it and if anything is wrong, please don't hesitate to let me know.😊

Best Regards,

Yuna

[yaman123]

Hi @Anonymous 

I used the date column and its worked. But it is also showing me all employees who started in that month instead of just promotions? 

[Anonymous]

Hi @yaman123 ,

Maybe I'm not getting what actually you are trying to do. 

Can you share some screenshots around the issue  and your desired result please? This is not helping much.

Add error screenshot as well which you are facing.

Best Regards,

Yuna

[yaman123]

Hi @Anonymous 

I would like to only show promotions for each employee who have had a promotion, and not to show all employees who have started in that month. 

I have attached what i would expect to see. 

So this shows employees who have had a promotion since Jan 2020 and this report was run in Aug 2020

[Anonymous]

Hi @yaman123 ,

You said that you only want to display promoted employees. In the example you provided earlier, do you only need to display the "B" and "D" lines?

If this is only the case, a new calculated table can solve the problem.

Table 3 = 

var x1=SUMMARIZE (

 'Table 2',

 'Table 2'[EMP NO ],

 'Table 2'[NAME],

 "OLD ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) < 3000 ), [JOB TITLE] ),

 "NEW ROLE", MAXX ( FILTER ( 'Table 2', YEAR ( 'Table 2'[VALID TO] ) > 3000 ), [JOB TITLE] ),

 "FROM DATE", MAX ( 'Table 2'[VALID FROM] )

)

return

FILTER(x1,[OLD ROLE]<>BLANK())

Best Regards,

Yuna

[yaman123]

Hi @Anonymous 

Yes, i want to display only promotions, so if the employees had an old role and new role. Also i would like to show the past 6 months of promotions. E.g if i run the report for Jan 2021, i would like to show promotions from Aug 2020 - Jan 2021 when i select Jan 2021 from the date slicer

[Anonymous · Accepted solution]

Hi @yaman123 ,

So you can do some steps as follows.

1. create a "Month" column in table "Date".

month = MONTH('Date'[Date])

2. create a measure and drag it into the filter of the table visual. (is 1)

Measure = 

var x1=SELECTEDVALUE('Date'[month])

var x2=MAXX('Table 3',MONTH('Table 3'[FROM DATE]))

return

IF(x2>=x1-6&&x2<=x1,1,0)

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

hI @Anonymous 

Can this work with Month Year instead of month? I am using a Month Year column in the date table for the rest of the report and would like to keep it consistent throughout? 

I have this formula which created the month year column

Month Year = FORMAT([Date],"mmmm")& " "&year([Date])

[Anonymous]

Hi @yaman123 ,

I don't think the "month year" column you gave can replace the "month" column. "Month year" column is "Text" type other than "whole number" Type. You'd better split the column and transform the type from "text" into "Whole number". Or you can recreate a month column like above.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

Can this work with Max(date,date) because the report is run end of the month?

[Anonymous]

Hi @yaman123 ,

 I did the test according to your request and unfortunately it failed.

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[yaman123]

No worries. Thanks for your help! 

[yaman123]

Hi @amitchandak 

I have the below dataset. The Valid To can be blank if the employee is still with the company.

EMP NO	NAME	JOB TITLE	VALID FROM	VALID TO
1001	A	MANAGER	19/05/2020	31/12/9999
1002	B	HR MANAGER	01/08/2012	31/07/2020
1002	B	HR BUSINESS PARTNER	01/08/2020	31/12/9999
1003	C	AP TEAM LEADER	05/01/2020	31/12/9999
1004	D	ADMINISTRATOR	01/10/2019	31/10/2020
1004	D	CONTRACT MANAGER	01/11/2020	31/12/9999

I would like to show the below when e.g August 2020 is selected:

EMP NO	NAME	OLD ROLE	NEW ROLE	FROM DATE
1002	B	HR MANAGER	HR BUSINESS PARTNER	01/08/2020

I have a date table which is used to select the month year and this isnt linked to any tables. I use MAX(DATE) in the measures to pull the data for that period.