Notifications
Clear all
Power BI
1
Posts
1
Users
0
Reactions
53
Views
Topic starter
Hello, Ive created a DAX formula to calculate the tenure of each staff member and want to average total to appear for each division, but for some reason it keeps displaying the 'newest' employee tenure for the month in the division average.
any assistance greatly received....... thank you kindly
DAX is:
Tenure Exp. =
VAR IsTotal = HASONEVALUE('Position_History _Merge'[orgpos__positiondefinition.OrgUnit2]) // Replace with your actual dimension
VAR DetailCalculation =
CALCULATE(
CALCULATE(
DATEDIFF(
MAX('Position_History _Merge'[adminbasedet__signifdates.HireDate]),
MAX('Calendar'[DateKey]),
DAY
) / 365, // TENURE CALC
FILTER(
'Position_History _Merge',
'Position_History _Merge'[EffectiveDate] <= LASTDATE('Calendar'[DateKey])
&& OR(
'Position_History _Merge'[EffectiveDate END] >= FIRSTDATE('Calendar'[Last Day of Month]),
'Position_History _Merge'[EffectiveDate END] = 0
)
)
),
CROSSFILTER('Position_History _Merge'[EffectiveDate], 'Calendar'[DateKey], NONE)
)
VAR AverageCalculation =
AVERAGEX(
VALUES('Position_History _Merge'[orgpos__positiondefinition.OrgUnit2]), // Replace with your actual dimension
CALCULATE(
CALCULATE(
DATEDIFF(
MAX('Position_History _Merge'[adminbasedet__signifdates.HireDate]),
MAX('Calendar'[DateKey]),
DAY
) / 365,
FILTER(
'Position_History _Merge',
'Position_History _Merge'[EffectiveDate] <= LASTDATE('Calendar'[DateKey])
&& OR(
'Position_History _Merge'[EffectiveDate END] >= FIRSTDATE('Calendar'[Last Day of Month]),
'Position_History _Merge'[EffectiveDate END] = 0
)
)
),
CROSSFILTER('Position_History _Merge'[EffectiveDate], 'Calendar'[DateKey], NONE)
)
)
RETURN
IF(IsTotal, AverageCalculation, DetailCalculation)
Posted : 11/06/2025 1:08 pm
