Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a running employee data month on month from Jan 2018. Now I have a new requirement to identify the number of promotions each month. I need to compare employee level this month vs previous month and if there is a change in employee level, then I need to mark them as a promotion.
In the below example there are 2 promotions in feb. how do I get this done on Power BI:
Emp ID | Emp Name | Emp Level | As of Date |
326 | John | 8 | January-19 |
294 | Patrick | 8 | January-19 |
252 | Mary | 7 | January-19 |
243 | Colm | 6 | January-19 |
283 | Ben | 6 | January-19 |
309 | Richard | 10 | January-19 |
298 | Philip | 10 | January-19 |
311 | Mike | 9 | January-19 |
253 | Roger | 9 | January-19 |
240 | Clara | 9 | January-19 |
314 | Rosy | 6 | January-19 |
326 | John | 8 | February-19 |
294 | Patrick | 8 | February-19 |
252 | Mary | 7 | February-19 |
243 | Colm | 5 | February-19 |
283 | Ben | 6 | February-19 |
309 | Richard | 10 | February-19 |
298 | Philip | 10 | February-19 |
311 | Mike | 8 | February-19 |
253 | Roger | 9 | February-19 |
240 | Clara | 9 | February-19 |
314 | Rosy | 6 | February-19 |
387 | Lara | 8 | February-19 |
Solved! Go to Solution.
did you try changing the date to the End of the Month as per my screenshot?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Sabarikumar7579 ,
Please try this calculated column.
tag = var pm=CALCULATE ( SUM ( Table1[Emp Level] ), FILTER ( ALLEXCEPT ( Table1, Table1[Emp ID], Table1[Emp Name] ), IF ( EARLIER ( Table1[As of Date].[MonthNo] ) <> 1, Table1[As of Date].[Year] = EARLIER ( Table1[As of Date].[Year] ) && Table1[As of Date].[MonthNo] = EARLIER ( Table1[As of Date].[MonthNo] ) - 1, Table1[As of Date].[Year] = EARLIER ( Table1[As of Date].[Year] ) - 1 && Table1[As of Date].[MonthNo] = 12 ) ) ) return IF(ISBLANK(pm),0,IF(pm<>'Table1'[Emp Level],1,0 ))
Here is the output.
More details, please refer to the attachment.
Best Regards,
Cherry
Hi,
i tweaked the same formula a bit by calling previous month variable before Prelevel and it worked.
Promoted = VAR pr = EOMONTH(Table1[As of Date],-2)+1 VAR PrevLevel = CALCULATE( max(Table1[Emp Level]), ALLEXCEPT(Table1, Table1[Emp ID], Table1[Emp Name]), Table1[As of Date] = pr ) RETURN IF( ISBLANK( PrevLevel ), FALSE(), PrevLevel <> [Emp Level] )
Can do this in DAX, but thought it would be better to do in PQ. Please take a look at the attached file and applied steps
But basically:
Here's the file:
https://1drv.ms/f/s!AoQIGRpzoxRHgbw6d8w25uXRk085Bg
-Nick
Hi Nick,
I did this approach in Power Query, however since the data is too huge (2 years data) and dint wanted to create another table.
I was more looking from a dax solution to make use of the existing data and not create another instance.
Regards
Sabari K
Huge is a relative term and there are ways to optimize and such, but can ( and looks like you have been provided solutions) in DAX. Though I am not a huge fan of EARLIER since that has been responisbile for some poor user experience. But it will work
have youy tried the solution i proposed? did it not work?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi @Sabarikumar7579 ,
you can do it via calculated column and then place a filter on TRUE to know how many people were promoted that month:
Promoted = VAR PrevLevel = CALCULATE( VALUES( Data[Emp Level] ), ALLEXCEPT(Data, Data[Emp ID]), Data[As of Date] = EOMONTH( EARLIER(Data[As of Date]), - 1 ) ) RETURN IF( ISBLANK( PrevLevel ), FALSE(), PrevLevel <> [Emp Level] )
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi,
I replicated your suggestion however, it's not showing those 2 true values. I also tried changing the data to start date of a month and altering the EOMONTH formula. still, it shows all false. Not sure where I am wrong.
below is the link to the file:
Hi @Sabarikumar7579 ,
Please try this calculated column.
tag = var pm=CALCULATE ( SUM ( Table1[Emp Level] ), FILTER ( ALLEXCEPT ( Table1, Table1[Emp ID], Table1[Emp Name] ), IF ( EARLIER ( Table1[As of Date].[MonthNo] ) <> 1, Table1[As of Date].[Year] = EARLIER ( Table1[As of Date].[Year] ) && Table1[As of Date].[MonthNo] = EARLIER ( Table1[As of Date].[MonthNo] ) - 1, Table1[As of Date].[Year] = EARLIER ( Table1[As of Date].[Year] ) - 1 && Table1[As of Date].[MonthNo] = 12 ) ) ) return IF(ISBLANK(pm),0,IF(pm<>'Table1'[Emp Level],1,0 ))
Here is the output.
More details, please refer to the attachment.
Best Regards,
Cherry
Hi,
The function too worked. however, i had already used the one mentioned above with few tweaks. Thank you for helping.
did you try changing the date to the End of the Month as per my screenshot?
Did I answer your question correctly? Mark my answer as a solution!
Proud to be a Datanaut!
Hi,
i tweaked the same formula a bit by calling previous month variable before Prelevel and it worked.
Promoted = VAR pr = EOMONTH(Table1[As of Date],-2)+1 VAR PrevLevel = CALCULATE( max(Table1[Emp Level]), ALLEXCEPT(Table1, Table1[Emp ID], Table1[Emp Name]), Table1[As of Date] = pr ) RETURN IF( ISBLANK( PrevLevel ), FALSE(), PrevLevel <> [Emp Level] )
User | Count |
---|---|
141 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
123 | |
101 | |
71 | |
61 |