Forum Discussion
Post PartisanEmployee Promotions
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.
Hi yaman123 ,
You can try it and if anything is wrong, please don't hesitate to let me know.😊
Best Regards,
Yuna
Post PartisanHi 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?
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
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
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]) 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?
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!