Complicated Dax

[Anonymous]

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.

[Ashish_Mathur]

Hi,

Share some data and show the expected result.

[Anonymous]

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.

[v-shex-msft]

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]

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.