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

Score big with last-minute savings on the final tickets to FabCon Vienna. Secure your discount

Reply
TapZxK
Helper II
Helper II

Help with making Headcount measure static

Hi Guys, 

I hope all are doing well, 
I have created a Headcount over time measure that looks like such:

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <= MAX ( 'Calendar'[Date] )
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= MIN ( 'Calendar'[Date] ),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

I also have a measure for Attrition over time:

 

Attrition =
CALCULATE (
[Headcount Over Time],
USERELATIONSHIP('Calendar'[Date], 'Resource Facts'[Resource Confirmed End Date])
)
 
I have inactive relationship set:
'Calendar'[Date] 1* 'Resource Facts'[Resource Start Date]
'Calendar'[Date] 1* 'Resource Facts'[Resource Confirmed End Date]
 
 

I want to create a attrition % measure which would look like such = Total Attrition / Total Headcount at the start of Fiscal Year (September 2021) 

I understand that I need to modify my headcount measure in a way that it always displays how many active people we had in a beginning of our fiscal year which starts on 01/Sep/2021
Not sure how to do so. 
Any help is much appreciated as always. 

BR,
Kris
1 ACCEPTED SOLUTION

@TapZxK , Try like

 

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <= startofyear( 'Calendar'[Date] ,"8/31")
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= startofyear( 'Calendar'[Date] ,"8/31"),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

 

or

 

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <=minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31"))
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31")),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@TapZxK , Usually in this measure we usually use both min and that means the start of the period. so if visual has FY, It will start of FY

 

otherwise for MIN ( 'Calendar'[Date] ) or Max ( 'Calendar'[Date] ) use 


startofyear( 'Calendar'[Date] ,"8/31")

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Hi @amitchandak 
let me elaborate a bit more.  I will include some screenshots below. 
So I have a visual that is showing me monthly rolling headcounts that is configured to only display numbers for FY22. This visual is using the [Headcount Over Time] measure described in my previous comment. 

TapZxK_0-1643367157809.png

What I want here is for the visual to display a September number of 127 in ALL of the months. 

So it should ignore the filter context and return only return the September number (127) in all of the months. 

BR,

Kris

 

@TapZxK , Try like

 

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <= startofyear( 'Calendar'[Date] ,"8/31")
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= startofyear( 'Calendar'[Date] ,"8/31"),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

 

or

 

Headcount Over Time =
CALCULATE (
COUNTROWS ( 'Resource Facts' ),
FILTER (
VALUES ( 'Resource Facts'[Resource Start Date] ),
'Resource Facts'[Resource Start Date] <=minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31"))
),
FILTER (
VALUES ( 'Resource Facts'[Resource Confirmed End Date] ),
OR (
'Resource Facts'[Resource Confirmed End Date] >= minx('Calendar', startofyear( 'Calendar'[Date] ,"8/31")),
ISBLANK ( 'Resource Facts'[Resource Confirmed End Date] )
)
)
)

Share with Power BI Enthusiasts: Full Power BI Video (20 Hours) YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

Check out the August 2025 Power BI update to learn about new features.

August 2025 community update carousel

Fabric Community Update - August 2025

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