Hi all,
First time posting so be gentle 🙂
I'm working in PowerBI Desktop and am struggling with a calculation for average headcount.
I have a table that contains staff numbers for every employee over the last 12 months, this is based on a monthly report so there are duplications. As an example, someone who has worked for us all year will appear 12 times as they're on every monthly report. Hope you're with me so far! Our financial year starts in April so our financial quarters are Apr/May/Jun = Q1, Jul/Aug/Sept = Q2 etc etc. What I am trying to do is calculate the average headcount for each quarter.
I've got a calculation that works - to an extent - I've encountered a problem though. It only works when I have a full quarters worth of data. Because I've only got 1 months worth of data for Q4 it's throwing my figures off.
This is the calculation I'm using, I achieved this with a mix of Google, YouTube and luck and like I say it works but falls down if there isn't enough data.
AVG Headcount by Qtr = VAR selectedDate = MAX('Date Table'[Date])
RETURN
SUMX('Staff in Post',
VAR EmpStartDate = [Date of Export]
var EndDate = [End Date]
return IF(EmpStartDate<= selectedDate &&
OR(EndDate>=selectedDate, EndDate=Blank()
),1,0)
)/3
I suspect the /3 is causing the issue but no idea how to fix it! For Q4, because we're only one month in it needs to be divide by 1. Once we update Feb then divide by 2. Hope that makes sense?
I can try and provide a copy of the data if that's needed - I work for the government though so it's always tricky sharing these things!
Thanks so much for any help you can give a DAX newbie!
