Forum Discussion

mmills2018's avatar
mmills2018
Icon for Helper IV rankHelper IV
5 years ago

Rolling 3 month calculation

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
Need Help

3 Replies

    • mmills2018's avatar
      mmills2018
      Icon for Helper IV rankHelper IV
      5 years ago

      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
      /
  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

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