Forum Discussion

jmaurer17's avatar
jmaurer17
Frequent Visitor
1 year ago
Solved

Help Needed: Issue with Compounding Contract Year Salary Increase Values

 

Hi everyone,

I'm encountering an issue with my DAX measure for calculating the cumulative percentage salary increase over multiple contract years. The measure is not correctly compounding the salary increase values. Here is the DAX code I'm using:

Cumulative % Salary Increase = 
VAR CY1Increase = SELECTEDVALUE('CY 1 Salary Inc'[CY 1 Salary Inc], 0) / 100
VAR CY2Increase = SELECTEDVALUE('CY 2 Salary Inc'[CY 2 Salary Inc], 0) / 100
VAR CY3Increase = SELECTEDVALUE('CY 3 Salary Inc'[CY 3 Salary Inc], 0) / 100

VAR ContractYear = SELECTEDVALUE(SPC[Contract Year], 1)  

RETURN 
SWITCH(
    TRUE(),
    ContractYear = 1, CY1Increase * 100,  
    ContractYear = 2, ((1 + CY1Increase) * (1 + CY2Increase) - 1) * 100,  
    ContractYear >= 3, ((1 + CY1Increase) * (1 + CY2Increase) * (1 + CY3Increase) - 1) * 100,  
    BLANK()
)

The goal is to correctly compound the salary increases for each contract year. However, the results I'm getting don't seem to reflect the expected compounded values.

I suspect there might be a precision point error affecting the calculations. All data types involved in the calculations are set to decimal numbers.

Could someone please help me identify what might be going wrong with this measure? Any insights or suggestions would be greatly appreciated!

Thank you in advance!

5 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    1 year ago

    Any particular reason for not using PRODUCTX ?

     

    Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
    Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
    Please show the expected outcome based on the sample data you provided.

    Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

  • jmaurer17's avatar
    jmaurer17
    Frequent Visitor
    1 year ago

    Appreciate the response. Here is the input and desired output:

    Contract Year Salary Inc input parameter Expected Cumulative % Increase

    CY 1 - 2.0%2.00%
    CY 2 - 3.0%5.06% (2% compounded with 3%)
    CY 3 - 3.0%8.14% (2% → 3% → 3% compounded)

     

    So the user a inputs a contract year salary increase percentage for one or all of the contract years. I get numbers close to this figure, but not exact - leading me to suspect a precision error. 

    • lbendlin's avatar
      lbendlin
      Super User
      1 year ago
      So the user a inputs a contract year salary increase percentage for one or all of the contract years

      How? How does the user input these numbers?

      • jmaurer17's avatar
        jmaurer17
        Frequent Visitor
        1 year ago

        The user inputs a percent increase into one or all (three) Contract Year Salary Inc parameters. I have a seperate numeric paramater for each contract year CY 1 through CY 3 in this case