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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Ajith_Kumar
Frequent Visitor

Count of employee last 3 month

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.

Screenshot 2023-08-07 162228.png



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

1 ACCEPTED SOLUTION
AmiraBedh
Resident Rockstar
Resident Rockstar

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 :
AmiraBedh_0-1691413225858.png

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

AmiraBedh_1-1691413296835.png

There is one Employee E that matches your condition

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696

View solution in original post

6 REPLIES 6
AmiraBedh
Resident Rockstar
Resident Rockstar

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 :
AmiraBedh_0-1691413225858.png

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

AmiraBedh_1-1691413296835.png

There is one Employee E that matches your condition

 


Proud to be a Power BI Super User !

Microsoft Community : https://docs.microsoft.com/en-us/users/AmiraBedhiafi
Linkedin : https://www.linkedin.com/in/amira-bedhiafi/
StackOverflow : https://stackoverflow.com/users/9517769/amira-bedhiafi
C-Sharp Corner : https://www.c-sharpcorner.com/members/amira-bedhiafi
Power BI Community :https://community.powerbi.com/t5/user/viewprofilepage/user-id/332696
Arul
Super User
Super User

@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.

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!


LinkedIn


Dhairya
Solution Supplier
Solution Supplier

Hey @Ajith_Kumar 

Do you have any Date column in your table?

No, only Month and Year column.

Can you @Ajith_Kumar please share expected output?

 

Hi @Dhairya,

Ajith_Kumar_0-1691476899516.png


Regards,
Ajith

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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