Measure in Another Measure

[smallwood0507]

Hello - first off I am very much still at the beginning of learning the world of PowerPivot and Dax.


For context I am working to calculate turnover/retention rates for employees sliced by tenure and avg hours. The column I am looking to return is the number of employees with hours 2 month prior to the filtered month in the pivot table. i.e if March 16 is selected it returns data for Jan 16, simple right? So far I can return this correctly for all employees and for calculations by tenure.


My issue is when I try to use a fairly complicated measure that calculates avg hours the employee worked. The measure returns the avg hours worked in the 3 prior months unless they have only worked in the current month, in which case it returns those hours


Avg Hours:=if(VALUE([Sum of EditedHours])>0,if(VALUE(CalculatE(DISTINCTCOUNT(CG_HT_Q[EditedHours]),
DATESINPERIOD('Calendar'[FirstDay],lastdate([FirstDay]),-4,MONTH)))=1,[Sum of EditedHours],
CALCULATE(sum(CG_HT_Q[EditedHours]),DATESINPERIOD('Calendar'[FirstDay],Lastdate(DATEADD('Calendar'[FirstDay],-1,MONTH)),-3,MONTH))/
Calculate(DISTINCTCOUNT(CG_HT_Q[EditedHours]),DATESINPERIOD('Calendar'[FirstDay],Lastdate(DATEADD('Calendar'[FirstDay],-1,MONTH)),-3,MONTH))),bLANK())


I then reference that measure in this formula which counts the distinct number of employees in the month 2 months prior.


EmployeesT-3 <80hrs:=Calculate(DISTINCTCOUNT(CG_HT_Q[Employee Name]),CG_HT_Q[EditedHours]>0,CG_HT_Q[LastScheduleMonth]>0,CG_HT_Q[Hire Date]>0,Filter(CG_HT_Q,[Avg Hours]<80),PREVIOUSMONTH(PREVIOUSMONTH('Calendar'[FirstDay])))


The formula in the current month (without the previousmonth formula) works fine, but I cannot get the one above to work..... I have tried the DATEADD formula as well instead of previousmonth.


Any ideas?
Thank you,
R