cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
novicenovice Regular Visitor
Regular Visitor

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 2
Community Support Team
Community Support Team

Re: Issue on variable within measurement - No result, No error

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.

 

61.png

 

 

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 Regular Visitor
Regular Visitor

Re: Issue on variable within measurement - No result, No error

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

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 299 members 2,616 guests
Please welcome our newest community members: