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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.