Power BI DAX count using multiple and hard conditions

[Noredlac86]

Hi Team.

I was wondering if someone can help me with my doubt.

I have a table why employes the month that they work and if they are the employee of the month:

For example In januay the employee of the mont was Jhon and Angela.

In March, only Jhon and Angela works and the employee of the month was Jhon.

Employ	ID	work in the month	employee of the month
Jhon	109	January	Yes
Angela	205	January	Yes
Rick	301	January	No
Jhon	109	Febrary	Yes
Angela	205	Febrary	Yes
Rick	301	Febrary	Yes
Jhon	109	March	Yes
Angela	205	March	No
Jhon	109	April	No
Angela	205	April	Yes
Rick	301	April	Yes

However i need to know in each month,How many times has an employee been Employee of the Month consecutively?For example (Please see last two columns):

Employ	ID	work in the month	employee of the month	How many times has an employee been Employee of the Month consecutively?	Wich Month?
Jhon	109	January	Yes	1
Angela	205	January	Yes	0
Rick	301	January	No	0
Jhon	109	Febrary	Yes	2	Febrary, January
Angela	205	Febrary	Yes	2	Febrary, January
Rick	301	Febrary	Yes	0
Jhon	109	March	Yes	3	March, Febrary, JAnuary
Angela	205	March	No	0
Jhon	109	April	No	0
Angela	205	April	Yes	0
Rick	301	April	Yes	2	Abril, Febrary

The idea is review per month and check if the last month the employee was "Employee of the month" 

In the example: In April, Jhon was not employee of the month, therefore we dont need to compare with the last months, Angela it was, but the month after april(March) was not employ of the month, theforefore its 0. 

Rick it was employee of the month in April, in march does not work (we only compare with the months that he work) until febrary that hi also was employee of the months, that means two consecutive months.

I know that his hard, but not sure if its possible to do it in powerbi?

Thanks!

pd: Sorry for my english is no my first language

[Kishore_Kadhir]

Hi @Noredlac86 ,

1. Create a calculated column with the below DAX.

Index = IF(Another[Month]="Jan", 1, IF(Another[Month]="Feb", 2))

2) Create another calculated column with the below DAX.

Which Month = IF('Another Table'[employee of month] = "Yes", CALCULATE(CONCATENATEX(VALUES('Another Table'[Month]), 'Another Table'[Month], ", "), FILTER(ALL('Another Table'), 'Another Table'[employee of month]="Yes" && 'Another Table'[Index] <= EARLIER('Another Table'[Index]))), BLANK())

Hope this solves your issue.

Regards,

Kishore

[Noredlac86]

HI.
Thanks for your time, one question how do you create "how many times" column?

[Kishore_Kadhir]

Hi @Noredlac86 ,

See the DAX below:

How Many Times = CALCULATE(COUNTROWS('Another Table'), FILTER(ALLEXCEPT('Another Table', 'Another Table'[EmployeeID]), 'Another Table'[employee of month]="Yes" && 'Another Table'[Index] <= EARLIER('Another Table'[Index])))

[Noredlac86]

Hi @Kishore_Kadhir 

Thanks for your help. I have created more records using this file

And i include the fomula tor how many times: 

How Many Times = CALCULATE(COUNTROWS('Another Table'), FILTER(ALLEXCEPT('Another Table', 'Another Table'[EmployeeID]), 'Another table'[Employee of the month]="Yes" && 'Another Table'[Index] <= EARLIER('Another Table'[Index])))

However in the results does not show correct, 
For example if i check July for Rick, should:
July(Yes) + Jun(Yes) + May(Yes) + April(Yes)+ Mar(Yes)+Feb(No) STOP =5
For example July Angela:

July (Yes) + June(No) STOP =1

The review will be only for consecutive months.



I was wondering if you can help me with this. im trying a lot of think, but im not an expert with this.

Thanks so much for your time.