Rolling 3 month calculation

[mmills2018]

Hello,

i have the below measure that functions correctly for rolling 3 month turnover.  The issue I am having is that if an associate terms at the end of a month, they are counting in that months headcount because there term would be effective the following month.  So, if i termed on 3/31/2021, i would be included in March's headcount and counted as a term in April.  For my term count, in my measure  I have: VAR LatestMonth = LASTDATE('Date Table'[Full Date]), so, its taking the end of the month terms.  any way i can set it up to say something like; LASTDATE('Date Table'[Full Date]-1day)?

Involuntary US Black Turnover =

VAR LatestMonth = LASTDATE('Date Table'[Full Date])

VAR Prior3rdMonth = FIRSTDATE(DATESINPERIOD('Date Table'[Full Date],LatestMonth,-4,month) )

return

CALCULATE(

DISTINCTCOUNTNOBLANK( 'Snapshots'[AssociateID] ),

'Snapshots'[Race_Ethnicity] = "Black Or African American (United States of America)",

'Snapshots'[Alt Term Date] <> BLANK(),

'Snapshots'[TerminationPrimaryTerminationCategory] = "Involuntary",

DATESINPERIOD(

'Date Table'[Full Date],

LatestMonth,

-3,

MONTH

)

) *4

/

((CALCULATE(DISTINCTCOUNTNOBLANK('Snapshots'[AssociateID]),'Snapshots'[Race_Ethnicity]="Black Or African American (United States of America)",'Snapshots'[Part of Month End Headcount?]="Yes",Prior3rdMonth)+CALCULATE(DISTINCTCOUNTNOBLANK('Snapshots'[AssociateID]),'Snapshots'[Race_Ethnicity]="Black Or African American (United States of America)",'Snapshots'[Part of Month End Headcount?]="Yes",LASTDATE('Snapshots'[Data as of])))/2)+0

[lbendlin]

maybe provide some sample data and show the expected outcome.  Likely you will need to protect the LASTDATE() with a CALCULATE() and a filter.

[lbendlin]

LASTDATE('Date Table'[Full Date])-1

[mmills2018]

I tried that but it excludes from all eom headcount, I want to include month end terms from my datesinperiod timefreame.  So, for rolling 3 month, I want to exclude those who termed 3/31/2021 but include 2/28 and 1/31.

DATESINPERIOD(

'Date Table'[Full Date],

LatestMonth,

-3,

MONTH

)

) *4

/