Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Complicated Dax

I am trying to have a projection column that in essence will say and do the following:

 

If a pay period has occured and not blank use what exists for that given month, if the pay period hasnt occured and is in the future assume it will use average of existing pay periods of that year.  my formula is as follows:  What is bolded is what needs to change.

 

Personnel=
SUMX (
    VALUES ( 'Calendar'[Month Name] ),
    IF (
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2022,
            '5. Chart of Accounts'[Object Category] = "Personnel",
            'Pay Period'[Pay Period] = "Yes"
        ) * -1
            = BLANK (),
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2021,
            '5. Chart of Accounts'[Object Category] = "Personnel",
            'Pay Period'[Pay Period] = "Yes"
        ) * -1,
        CALCULATE (
            SUMX ( '6. Actuals', '6. Actuals'[Actuals] ),
            '6. Actuals'[Actuals],
            'Calendar'[Year] = 2022,
            '5. Chart of Accounts'[Object Category] = "Personnel",
            'Pay Period'[Pay Period] = "Yes"
        ) * -1))
 
What it should be is lets say we have been paid for 10 pay periods with the average being 10$; pay periods 11-26 should display $10.
 
Thanks for help I think what is throwing off is values formula at start and my inability to get the average to work in this appropratiely.
Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Ideally what should be in bolded is the verbal version of 

     

    Average of 2022 Actuals that occured on a pay period date that are personnel expenses.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous.

      You can try to use the following measure formula if it is suitable for your requirement:

      Personnel =
SUMX (
    VALUES ( 'Calendar'[Month Name] ),
    CALCULATE (
        VAR currPay =
            MAX ( Table[pay period] )
        RETURN
            IF (
                CALCULATE ( SUM ( '6. Actuals'[Actuals] ), 'Calendar'[Year] = 2022 )
                    = BLANK (),
                CALCULATE (
                    IF (
                        currPay >= 11
                            && currPay <= 26,
                        10,
                        IF (
                            currPay = 10,
                            AVERAGE ( '6. Actuals'[Actuals] ),
                            SUM ( '6. Actuals'[Actuals] )
                        )
                    ),
                    'Calendar'[Year] = 2021
                ),
                CALCULATE (
                    IF (
                        currPay >= 11
                            && currPay <= 26,
                        10,
                        IF (
                            currPay = 10,
                            AVERAGE ( '6. Actuals'[Actuals] ),
                            SUM ( '6. Actuals'[Actuals] )
                        )
                    ),
                    'Calendar'[Year] = 2022
                )
            ),
        '6. Actuals',
        '5. Chart of Accounts'[Object Category] = "Personnel",
        'Pay Period'[Pay Period] = "Yes"
    )
)

      Regards,

      Xiaoxin Sheng

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        I think this is close!  What I want is instead of 1 appearing like we have in the above formula (i think you have ten) I would want the current year's average to appear at all the Blank pay dates that are "Yes" that have not occured.  Addiitionally the summing of the formula should equal roughly 70m not 503m but I have not figured out why that is summing that way.  Also, I tried replacing the 1 with an average formula but that did not work either.