Forum Discussion
Modeling Year-over-Year Total Compensation Costs (with compounding inputs)
Help? I'm calculating total compensation costs using positions, base salaries, benefits, and modifiers, and running into trouble with cumulative calculations from one year to the next (fiscal year). I'm given inputs like the following. (Modifications not marked as Excluded are cumulative (additive, compounding), so a COLA increase this year builds on the COLA from last year. Also, altogether different calculations (such as retirement) are calculated off only those modifications not marked as Excluded.)
Typical InputsI'm pretty sure this is all about setting up the SUMX or similar aggregating function properly, however, there's a lot of moving parts, and I'm stuck. I need to end up with reporting like the following hypothetical examples--at different granularities. Thankfully, I don't have constraints on how I present the results--my interest is limited to building the data model.
Typical Totals by Position, Fiscal Year, and Modifier
Typical Summary of Total Compensation CostsCalculating the compounded percentage for a particular year hasn't been a problem (I don't think) using a formula like the one below. However, I'm stuck when it comes to using the product to calculate the salary basis for the following fiscal year (compounding).
CALCULATE (
EXP (
SUMX (
FILTER (
BUModifiers,
COUNTROWS (
FILTER (
ALLSELECTED ( 'Calendar'[FiscalYear] ),
ISONORAFTER ( 'Calendar'[FiscalYear], MAX ( 'Calendar'[FiscalYear] ), DESC )
)
)
),
LN ( 1.0 + ABS ( BUModifiers[Percent Adjusted] ) )
* IF ( BUModifiers[Percent Adjusted] < 0, -1, 1 )
)
)
- 1.0,
BUModifiers
)Any ideas on how to proceed? Suggestions? Help?
1 Reply
- v-chuncz-msft
Community Support
You may check if the following articles help.