I have a dataset of former employees spread over 20 years with leaver reasons, I have a column created which flags whether the leave reason is voluntary or compulsory. I am trying to create a count which will count all voluntary leavers for the last 12 calendar months. The table is called 'Staff Data'. Example data below.
|LEAVE REASON||VOLUNTARY?||LEAVE DATE|
|Conflict with manager||yes||10/01/2021|
In the example data I'd like result to be 4 (Retirement in May-21, Higher pay in Aug-21 and Oct-20, Conflict with manager in Jan-21).
I have a 'Date' table created and in this table I have create a measure (LTM Dates) which should flag only the last 12 months, using 2 other measures (StartDate and EndDate).
StartDate = EDATE(EOMONTH(today(),-1),-12)+1
EndDate = EOMONTH(today(),-1)
Using the above I have this measure:
LTM Dates = IF(MAX('Date'[Date])>= [StartDate]&&MAX('Date'[Date])<=[EndDate],true(),false())
In order to count the rows I need including I know I need to filter and this is what I've got so far but it's not working. It would be great if you could point out where I'm going wrong please.
CountVol.LeaverLTM = COUNTROWS(CALCULATETABLE('Date',FILTER('Date','Date'[LTM Dates]=TRUE),FILTER('Staff Data','Staff Data'[Vol. Leaver]= "yes")))