Forum Discussion

novicenovice's avatar
novicenovice
Helper I
6 years ago

Issue on variable within measurement - No result, No error

 

Dear all,

 

I have an issue on my calculation of my variable IDDLP in Fig 1 which determinate the max date and used directly in  my measurement fig3. The calculation run run whithout providing the result and without showing any error message but when I use the code in fig2 the calculation return the result quickly.

 

on Fig1 the issue remains even if the condition of the switch return 1

 

Fig1

 

           VAR IDDTLP= SWITCH(IDVSTYPE
                ;1;IDDT
                ;2;IDDT
                ;3;IDDT
                ;4;IDDT
                ;5;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;V_DATA_PORTFOLIO[DATE_BASE_PRICE]<IDDT&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;6;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;7;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;8;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])<=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;IDDT
    )      

 

 

fig 2

         VAR IDDTLP= IDDT

fig 3

BASE_VOLUME_PRICE_INDEX_REF = 
VAR IDINDICATOR = MIN(SELECT_VOLUME[VOLUME_ID])  var IDPERIODTYPE = MIN(SELECT_IND_TYPE[ID_SELECT_IND_TYPE])
VAR IDPERIODICITY = MIN(PERIOD_TYPE[PERIOD_TYPE_ID])
VAR IDDT = [DEFAULT_REF_DATE]
VAR IDDTYEAR = YEAR(IDDT)
VAR IDDTBEGINNING = [DEFAULT_REF_DATE_BEGINNING]
VAR IDVSTYPE = [ID_VS_TYPE_DEFAULT]

VAR IDRESULT = CALCULATE(SUMX(
    GROUPBY(SELECTCOLUMNS(V_DATA_PORTFOLIO;"CUST";RELATED(TR_CUSTOMER[CUST_BASE_PRICE]);"SKU";RELATED(TR_MATERIAL[SKU_BASE_PRICE]))
    ;[CUST];[SKU]);
    VAR IDCUST = [CUST]
    VAR IDSKU = [SKU]
    VAR IDSKUSPECIALVOLUME = LOOKUPVALUE(T_MATERIAL_PI_IND_SOURCE[IND_PI];T_MATERIAL_PI_IND_SOURCE[MATERIAL];IDSKU)

           VAR IDDTLP= SWITCH(IDVSTYPE
                ;1;IDDT
                ;2;IDDT
                ;3;IDDT
                ;4;IDDT
                ;5;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;V_DATA_PORTFOLIO[DATE_BASE_PRICE]<IDDT&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;6;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;7;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;8;CALCULATE(MAX(V_DATA_PORTFOLIO[DATE_BASE_PRICE]);FILTER(V_DATA_PORTFOLIO;YEAR(V_DATA_PORTFOLIO[DATE_BASE_PRICE])<=IDDTYEAR&&V_DATA_PORTFOLIO[LASTDT]=TRUE()&&RELATED(TR_MATERIAL[SKU_BASE_PRICE])=IDSKU&&RELATED(TR_CUSTOMER[CUST_BASE_PRICE])=IDCUST))
                ;IDDT
    )            

    VAR IDDTLPBEGINNING = if(IDVSTYPE in {5;6;7;8};DATE(YEAR(IDDTLP);MONTH(IDDTLP);1);IDDTBEGINNING)


    VAR IDVOLUME = SWITCH(IDSKUSPECIALVOLUME
                        ;"M2"; CALCULATE([Portf M2 - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;"M3";CALCULATE([Portf M3 - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;"QTY";CALCULATE([Portf QTY - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                        ;CALCULATE([Portf Tons - Corr - Without Past Open];DATESBETWEEN('Calendar'[Date];IDDTLPBEGINNING;IDDTLP))
                       )

RETURN IDVOLUME )))

return IDRESULT

Thanks for your help

 

Novice

 

2 Replies

  • v-xicai's avatar
    v-xicai
    Community Support

    Hi novicenovice ,

     

    You may need to sort out the formula's logic, there are too much VAR statements nested in the SUMX and GROUPBY function. You can use VAR statement one by one, the next VAR statement can refer to the last VAR statement, then use a RETURN statement to return the final result.

     

     

     

    Best Regards,

    Amy

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • novicenovice's avatar
      novicenovice
      Helper I

      Hello Amy,

       

      I'm sorry but I don't understand your recommendation as my english is quite basic.

       

      Could you relaborate it by providing an example ?

       

      Thanks

       

      Novice