cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
drewmcspy
Frequent Visitor

Countrows and filtering

Hi All

 

I have a dataset of former employees spread over 20 years with leaver reasons, I have a column created which flags whether the leave reason is voluntary or compulsory. I am trying to create a count which will count all voluntary leavers for the last 12 calendar months. The table is called 'Staff Data'.  Example data below.

 

LEAVE REASONVOLUNTARY?LEAVE DATE
Redundancyno20/12/2020
Career Changeyes20/12/2018
Retirementyes15/05/2021
Dismissedno06/08/2021
Higher payyes16/04/2020
Higher payyes09/08/2021
Conflict with manageryes10/01/2021
Higher payyes10/10/2020
Dismissedno12/11/2020


In the example data I'd like result to be 4 (Retirement in May-21, Higher pay in Aug-21 and Oct-20, Conflict with manager in Jan-21).

 

I have a 'Date' table created and in this table I have create a measure (LTM Dates) which should flag only the last 12 months, using 2 other measures (StartDate and EndDate).

 

StartDate = EDATE(EOMONTH(today(),-1),-12)+1
EndDate = EOMONTH(today(),-1)

Using the above I have this measure:

LTM Dates = IF(MAX('Date'[Date])>= [StartDate]&&MAX('Date'[Date])<=[EndDate],true(),false())


In order to count the rows I need including I know I need to filter and this is what I've got so far but it's not working. It would be great if you could point out where I'm going wrong please.

 

CountVol.LeaverLTM = COUNTROWS(CALCULATETABLE('Date',FILTER('Date','Date'[LTM Dates]=TRUE),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))

 

Thanks

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@drewmcspy , You are using date table, so you have active relation between date of Date table and leave date

 

measure =
var _min = EDATE(EOMONTH(today(),-1),-12)+1
vat _max = EOMONTH(today(),-1)
return COUNTROWS(CALCULATETABLE('Date',FILTER('Date','Date'[LTM Dates] <= _max && 'Date'[LTM Dates] >=_min),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))

 

refer how to handle two dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

View solution in original post

2 REPLIES 2
amitchandak
Super User
Super User

@drewmcspy , You are using date table, so you have active relation between date of Date table and leave date

 

measure =
var _min = EDATE(EOMONTH(today(),-1),-12)+1
vat _max = EOMONTH(today(),-1)
return COUNTROWS(CALCULATETABLE('Date',FILTER('Date','Date'[LTM Dates] <= _max && 'Date'[LTM Dates] >=_min),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))

 

refer how to handle two dates

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Thanks. I had an extra problem with TRUE/FALSE values but managed to sort it thanks to the extra link you shared.

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors