Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello All,
I am trying to create a measure that will allow me to calculate a trailing 30, 90, and 365 day head count and attrition figures. My data is a list of employees by name, ID and their Status (active or Terminated). I also have a date table with a "DayOffset" field. This reads as "0" = today, -1 = yesterday, -30 = 30 days ago and so on...My formula that I am trying for the trailing 30 Headcount calculation is the following but not quite getting it done:
"
Solved! Go to Solution.
Hi @Anonymous
Try
a =
CALCULATE (
DISTINCTCOUNT ( 'Headcount - Trailing'[Employee Number] ),
'Headcount - Trailing'[Status] = "Active",
'eis Date'[DayOffset] >= -30,
'eis Date'[DayOffset] <= 0
)
This assumes a proper 1-Many relationship between 'eis Date' and the fact table
If it does not work dp share a sample of the tables involved
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Hi @Anonymous
Try
a =
CALCULATE (
DISTINCTCOUNT ( 'Headcount - Trailing'[Employee Number] ),
'Headcount - Trailing'[Status] = "Active",
'eis Date'[DayOffset] >= -30,
'eis Date'[DayOffset] <= 0
)
This assumes a proper 1-Many relationship between 'eis Date' and the fact table
If it does not work dp share a sample of the tables involved
|
Please accept the solution when done and consider giving a thumbs up if posts are helpful. Contact me privately for support with any larger-scale BI needs, tutoring, etc. |
Thanks A|B that did the trick!
Check out the July 2025 Power BI update to learn about new features.