I have a piece of code in DAX that "works" perfectly. However if I add an extra filter inito the code, then it goes a bit pear shaped. For the life of me I ave tried to fix this and failed miserably. Any suggestions welcome 🙂
The code below. This works perfect - counting staff at month end each month. BUT, only if no slicers are active to filter "Business Area".
EmployeesAtEndOfMonth =
IF(
ISFILTERED('staffmaster'[Business Area]),
CALCULATE(
DISTINCTCOUNT('staffmaster'[Unique ID]),
FILTER(
ALL('staffmaster'),
'staffmaster'[Business Area] IN VALUES ('STAFFMASTER'[Business Area]) &&
('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) &&
'staffmaster'[Start Date] <= MAX('Date'[End of Month])
)
),
CALCULATE(
DISTINCTCOUNT('staffmaster'[Unique ID]),
FILTER(
ALL('staffmaster'),
('staffmaster'[End Date] >= MAX('Date'[End of Month]) || ISBLANK('staffmaster'[End Date])) &&
'staffmaster'[Start Date] <= MAX('Date'[End of Month])
)
)
)
If I select a "Business Area" in a slicer. Lets for example say "HR", then I get some odd behavour. If the month in question did not have someone new starting that month, it doesnt count anything for that month.
Example -
Jan23 - 30 staff (Powerbi counts 30 staff because someone new started month)
Feb 23 - 37 staff (because at least one new start that month)
Mar23 - 0 staff (no new starts this month so powerbi just counts the month as blank)
April 23 - 35 staff (at least one new start)
May23 - 0 staff (no new starts this month)
Jun23 - 36 staff (at least one new start)
So the only correlation between the blanks is that we had no new starts in "HR" in the blank months. I have check against other Business Areas and the correlation seems to hold up. ANy business area where we had a least one new start each month, has no blanks. Any business area where we had no new starts in any month, those months show a blank.
Im no PBI master and I expect the answer is simple enough, but im stumped.
As for data tables the data comes from:
Date Table - marked as date table, called "Date". Has columns for [Date], [End of Month]
Staff table - called Staffmaster. Has columns for [Unique ID], [Start Date], [End Date], [Business Area].
All i want to do is show a number of staff employed on the last day of each month, and be able to filter it by business area.
