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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

Get number of promotes in this current month

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 IDEmp NameEmp LevelAs of Date
326John8January-19
294Patrick8January-19
252Mary7January-19
243Colm6January-19
283Ben6January-19
309Richard10January-19
298Philip10January-19
311Mike9January-19
253Roger9January-19
240Clara9January-19
314Rosy6January-19
326John8February-19
294Patrick8February-19
252Mary7February-19
243Colm5February-19
283Ben6February-19
309Richard10February-19
298Philip10February-19
311Mike8February-19
253Roger9February-19
240Clara9February-19
314Rosy6February-19
387Lara8February-19
3 ACCEPTED SOLUTIONS

@Sabarikumar7579 

 

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!  

View solution in original post

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.

 

Capture.PNG

 

More details, please refer to the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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] )
 

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

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:

  1. Sort the table by emp ID and date (descending)
  2. Group by Emp ID
  3. Add in Index column to each new table starting at 0 and 1
  4. Merge each table with itself
  5. This brings in the Previous Emp level
  6. If statement that if the prev emp level and current emp level <>, give an "x" otherwise null
    1. That's your Promoted flag

Final Table.png

 

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

Anonymous
Not applicable

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 

@Sabarikumar7579 

 

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!  

LivioLanzo
Solution Sage
Solution Sage

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] )

 

Capture.PNG

 


 


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:

 https://1drv.ms/u/s!Asht7QhPGrBBiVyojypT-FKv2Hds

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.

 

Capture.PNG

 

More details, please refer to the attachment.

 

Best Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi,

The function too worked. however, i had already used the one mentioned above with few tweaks. Thank you for helping. 

@Sabarikumar7579 

 

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] )
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.