I have been working on a calculated "start of month" count and "end of month" count to calculate average headcount. what I am trying to do is: IF AND[EE_List(adjhiredate)<STARTOFMONTH +1], AND[EE_List(termdate)>STARTOFMONTH]1, 0.
I've also tried it with my [Calendar(StartMonthDay)] but still can't get both variables to work. I'll need to do the same for my "end of month" count.
Current Try....
Active1stDayofMonth =
SWITCH(
TRUE(),
EE_List[AdjHireDate]<'Calendar'[StartDayMonth]+1
&& EE_List[TermDate]>'Calendar'[StartDayMonth],
1,
0)
Please help, I've tried &&, and, AND, if, IF. Other formula's tried:
1. EmpCount = CALCULATE(COUNT([EmplID]), FILTER(ALL('Calendar'[FiscalYearMonthNumber]), 'Calendar'[FiscalYearMonthNumber] = MAX('Calendar'[FiscalYearMonthNumber])))
2. Active1stDayofMonth =
SWITCH(
TRUE(),
EE_List[AdjHireDate]<'Calendar'[StartDayMonth]+1
&& EE_List[TermDate]>'Calendar'[StartDayMonth],
1,
0)
AverageHeadcount = ([Start of Month]+[End of Month])/2
End of Month = CALCULATE (
COUNT ( 'EE_List'[EmplID]),
FILTER (
ALL ( 'EE_List' ),
COUNTROWS (
FILTER (
'EE_List',
NOT ISBLANK ( [TermDate] )
&& MONTH ( EARLIER( ( EE_List[AdjHireDate] ) )
= MONTH ( MAX ( 'Calendar'[EndofMonth] )
)
)))))
Start of Month =
CALCULATE (
COUNT ( EE_List[EmplID] ),
FILTER (
ALL ( EE_List),
COUNTROWS (
FILTER (
'EE_List',
NOT ISBLANK ( [TermDate] )
&& MONTH ( EARLIER ( 'EE_List'[AdjHireDate]) ) = MONTH ( MAX ('Calendar'[StartofMonth])
)
)
)
))
3. EmpCount2 =
VAR EarliestDate = CALCULATE(MIN('Calendar'[Date]),ALLSELECTED())
RETURN CALCULATE(DISTINCTCOUNT('EE_List'[Active_Inactive]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date] <= MAX('Calendar'[Date]) && 'Calendar'[Date] >= EarliestDate))
4. Count of Active Employee =
VAR currentDate =
MAX ( 'Calendar'[Date])
RETURN
CALCULATE (
COUNTROWS ( EE_List ),
FILTER (
EE_List,
( EE_List[AdjHireDate] <= currentDate
&& EE_List[TermDate] >= currentDate )
&& EE_List[Active_Inactive] = 1
)
)
