cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 REASON VOLUNTARY? LEAVE DATE Redundancy no 20/12/2020 Career Change yes 20/12/2018 Retirement yes 15/05/2021 Dismissed no 06/08/2021 Higher pay yes 16/04/2020 Higher pay yes 09/08/2021 Conflict with manager yes 10/01/2021 Higher pay yes 10/10/2020 Dismissed no 12/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
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...

2 REPLIES 2
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...

Frequent Visitor

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

Announcements

#### 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 Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

#### New forum boards available in Real-Time Intelligence.

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

Top Solution Authors
Top Kudoed Authors