Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
Hi all,
I am trying to calculate previous months headcount. I have the below to calculate the headcount per month
Solved! Go to Solution.
Hi, @yaman123
According to your description, if you have month and year column in date table, you can try like this:
Headcount =
VAR year =
SELECTEDVALUE ( 'Date'[year] )
VAR month =
SELECTEDVALUE ( 'Date'[month] )
VAR date =
DATE ( year, month, 1 )
VAR previousfirstday =
EDATE ( date, -1 )
VAR previouslastday =
EOMONTH ( date, -1 )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Headcount by Function'[EMP_NO] ),
'Headcount by Function'[VALID_FROM] >= previousfirstday
|| ISBLANK ( 'Headcount by Function'[VALID_TO] ),
'Headcount by Function'[VALID_TO] <= previouslastday
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @yaman123
According to your description, if you have month and year column in date table, you can try like this:
Headcount =
VAR year =
SELECTEDVALUE ( 'Date'[year] )
VAR month =
SELECTEDVALUE ( 'Date'[month] )
VAR date =
DATE ( year, month, 1 )
VAR previousfirstday =
EDATE ( date, -1 )
VAR previouslastday =
EOMONTH ( date, -1 )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Headcount by Function'[EMP_NO] ),
'Headcount by Function'[VALID_FROM] >= previousfirstday
|| ISBLANK ( 'Headcount by Function'[VALID_TO] ),
'Headcount by Function'[VALID_TO] <= previouslastday
)
If it doesn’t solve your problem, please feel free to ask me.
Best Regards
Janey Guo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Can you try the following formula.
Previous Month Count = CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]),DATESMTD(DATEADD('Date'[Date],-1,MONTH)))
OR
Previous Month Count = CALCULATE(DISTINCTCOUNT('Headcount by Function'[EMP_NO]) PREVIOUSMONTH('Date'[Date]))
This doesnt work unfortunately. It brings back all employees even those who have left. I only need to show employees who were with the business last month
Do you want selecteddate to be the last date (31/01/2021) of previous month?
If this is the case then try the following formula:
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
117 | |
84 | |
49 | |
38 | |
28 |
User | Count |
---|---|
185 | |
73 | |
73 | |
50 | |
42 |