Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

SUMX Iterating over 2 years

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

Need Help

2 Replies

  • v-yuta-msft's avatar
    v-yuta-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      Anonymous
      Not applicable
      7 years ago

      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