Get number of promotes in this current month

[Sabarikumar7579]

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

[LivioLanzo · Accepted solution]

@Sabarikumar7579 

did you try changing the date to the End of the Month as per my screenshot?

[v-piga-msft · Accepted solution]

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

[Anonymous]

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

Here's the file:

https://1drv.ms/f/s!AoQIGRpzoxRHgbw6d8w25uXRk085Bg

-Nick

[Sabarikumar7579]

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]

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 

[LivioLanzo]

@Sabarikumar7579 

have youy tried the solution i proposed? did it not work?

[LivioLanzo]

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

[Sabarikumar7579]

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

[v-piga-msft · Accepted solution]

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

[Sabarikumar7579]

Hi,

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

[LivioLanzo · Accepted solution]

@Sabarikumar7579 

did you try changing the date to the End of the Month as per my screenshot?

[Sabarikumar7579 · Accepted solution]

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