SUMX Iterating over 2 years

[Anonymous]

Hi all,

I have following problem on this Data model. sample

The measure Effect Product_ALLEX = SUMX(
SUMMARIZE (
    'Sales',
    'Calendar'[***Quarter],
    'CDW C_MARKET_HIERARCHIES_ALL_V'[LEVEL4],
    'CDW C_PRODUCTS_V'[PROD_CD]   
),[Delta Units]*[Price Diff PY Product - Brand_ALLEX])

is working liked wished.

The problem is that obviously SUMX is not executed for a code which have only existed in previous Year quarter but not in selected one. E.g ‘20552’ in Finland. Any hints how I can adjust the SUMX iterator so it that it would calculate for 'PROD_CD' which have values in selected quarters and in corresponding previous year quarters?

Thanks a lot in advance

BR

Nico

[v-yuta-msft]

Hi NicoD82,

To be general, maybe you can try formula below:

Effect Product_ALLEX =
CALCULATE (
    SUMX (
        SUMMARIZE (
            'Sales',
            'Calendar'[***Quarter],
            'CDW C_MARKET_HIERARCHIES_ALL_V'[LEVEL4],
            'CDW C_PRODUCTS_V'[PROD_CD]
        ),
        [Delta Units] * [Price Diff PY Product - Brand_ALLEX]
    ),
    ALLSELECTED ( 'CDW C_PRODUCTS_V'[PROD_CD] )
)

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy Tao,

thanks for your suggestion. Unfortunately this doesn't give me the expected result. The iterator for SUMX should just consider Codes from previous year as well. It sounds so simple but I didn't find a way to achieve it...

BR

Nico