Count of employee last 3 month

[Ajith_Kumar]

Hi All,

I have table like the below mentioned image. My requirement is count of emp_Id whoever is getting zero(0) value for last 3 month.





Kindly help me on this if anyone have any idea let me know.
@Arul  @Greg_Deckler @amitchandak 
Regards,
Ajith

[AmiraBedh · Accepted solution]

So I created a MonthOrder table since you have the month in text and year (I suggest to improve that)

MonthOrder =
DATATABLE(
"Month", STRING,
"MonthOrder", INTEGER,
{
{ "JAN", 1 },
{ "FEB", 2 },
{ "MAR", 3 },
{ "APR", 4 },
{ "MAY", 5 },
{ "JUN", 6 },
{ "JULY", 7 },
{ "AUG", 8 },
{ "SEP", 9 },
{ "OCT", 10 },
{ "NOV", 11 },
{ "DEC", 12 }
}
)

and then I linked it to the Data table :

Then I created the following measure :

ZeroCountLast3Months =
VAR Last3MonthsTable =
TOPN(
3,
SUMMARIZE(
'Data',
'Data'[Month],
"MonthOrder", CALCULATE(MAX('MonthOrder'[MonthOrder]))
),
[MonthOrder], DESC
)
VAR Last3Months = SELECTCOLUMNS(Last3MonthsTable, "Month", [Month])
VAR EmployeesWithZerosLast3Months =
SUMMARIZE(
FILTER(
'Data',
'Data'[Month] IN Last3Months && 'Data'[Value] = 0
),
'Data'[EMP_ID],
"ZeroCount", COUNTROWS('Data')
)
RETURN
COUNTROWS(
FILTER(EmployeesWithZerosLast3Months, [ZeroCount] = 3)
)

There is one Employee E that matches your condition

[Arul]

@Ajith_Kumar ,

Try the below,

1. Created a calculated column for the dates using the below code,

FirstDayOFMonth = DATEVALUE('Table'[MONTH]&"-"&'Table'[YEAR])

2. Try the below measure for employees count those have values zero for last 3 month.

Count Of Employee Id = 
VAR _date = 
              MONTH(MAX('Table'[FirstDayOFMonth]))
VAR _currentMonth = 
                FILTER(
                    'Table',
                    MONTH('Table'[FirstDayOFMonth]) = _date &&
                    'Table'[VALUE] = 0)
VAR _previousMonth = 
               FILTER(
                    'Table',
                    MONTH('Table'[FirstDayOFMonth]) = _date-1 &&
                    'Table'[VALUE] = 0)
VAR _monthBeforePrevious = 
               FILTER(
                    'Table',
                    MONTH('Table'[FirstDayOFMonth]) = _date-2 &&
                    'Table'[VALUE] = 0)
VAR _allTogether = EXCEPT(EXCEPT(_currentMonth,_previousMonth),_monthBeforePrevious)
RETURN COUNTROWS(_allTogether)

I am getting count as 1 for the sample data provided, however can you check and confirm whether it is working as expected.

[Dhairya]

Hey @Ajith_Kumar 

Do you have any Date column in your table?

[Ajith_Kumar]

No, only Month and Year column.

[Dhairya]

Can you @Ajith_Kumar please share expected output?

[Ajith_Kumar]

Hi @Dhairya,

Regards,
Ajith