Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
This is a continuation of a post from a while back. You can find the link below. A problem that I am running in to with this formula is that employees who are active for only part of the month don't seem to be counted. I want those to be included. Any employee that is active during the month. How do I change the formula so that instead of current date, it looks for the first day of this month and last day of this month? (see code below). The table name is "Master" and columns are "start date" and "end date". There is an "active?" column, but I don't think this is needed in this instance.
Count of Active Employee =
VAR currentDate =
MAX ( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Master ),
FILTER (
ALL(Master),
( Master[Start Date] <= currentDate
&& Master[End Date] >= currentDate )
Solved! Go to Solution.
So, say we are looking at April 2017. currentDate is going to be Apr 30, 2017 (max date in April).
FILTER (
ALL(Master),
Master[Start Date] <= currentDate && Master[End Date] >= currentDate
)
Give me everybody that started on or before Apr 30 (that seems fine) AND ended on or after Apr 30 (not ok)?
So, if somebody ended Apr 15, they aren't included (because Apr 15 is not >= Apr 30).
How about:
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Master ),
FILTER (
ALL(Master),
( Master[Start Date] <= endOfPeriod
&& Master[End Date] >= startOfPeriod)
)
)
So, say we are looking at April 2017. currentDate is going to be Apr 30, 2017 (max date in April).
FILTER (
ALL(Master),
Master[Start Date] <= currentDate && Master[End Date] >= currentDate
)
Give me everybody that started on or before Apr 30 (that seems fine) AND ended on or after Apr 30 (not ok)?
So, if somebody ended Apr 15, they aren't included (because Apr 15 is not >= Apr 30).
How about:
Count of Active Employee =
VAR endOfPeriod =MAX ( 'DateTable1'[Date] )
VAR startOfPeriod = MIN( 'DateTable1'[Date] )
RETURN
CALCULATE (
COUNTROWS ( Master ),
FILTER (
ALL(Master),
( Master[Start Date] <= endOfPeriod
&& Master[End Date] >= startOfPeriod)
)
)
Worked perfectly. Thanks so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |