Forum Discussion

LukeWalker's avatar
LukeWalker
Advocate IV
8 years ago

Variable Value Changes Based on Return Expression Bug

I have the following measure:

Y/Y Price Inflation = 
VAR MaxDate = MIN(MAX('Calendar'[Date]),TODAY())
RETURN
CALCULATE(
        VAR Num = [Cumulative Price Inflation]
        VAR Denom = CALCULATE([Cumulative Price Inflation],FILTER(ALL('Calendar'),'Calendar'[Date]<MaxDate-365))
    RETURN
        DIVIDE(Num-Denom,Denom+1),
    FILTER(LookBehind,LookBehind[n]=1)
)

 

The issue is the RETURN statement on line 8. If I only reference the Num variable (RETURN Num), I get the expected result. However, if I reference Num and Denom (RETURN Num-Denom) or any other combination with both variables, a different value is used for Num, all else equal. I am not sure where this other result is coming from. Could this be some context bug or something? I'm at my wit's end on this one. I am running the May 2018 version.

 

Cumulative Price Inflation = 
VAR MaxDate = MIN(MAX('Calendar'[Date]),TODAY())
VAR LookBack = MAX(LookBehind[Days])
VAR AllCal = FILTER(ALL('Calendar'),'Calendar'[Date]<=MaxDate)
VAR ThisPer = FILTER(AllCal,'Calendar'[Date]>MaxDate-LookBack)
RETURN
DIVIDE(
    SUMX(item_all,
        VAR MinPODate = CALCULATE(MIN(po_mst[order_date]),AllCal)
        VAR MaxPODate = CALCULATE(MAX(po_mst[order_date]),AllCal)
        VAR RelatedPO = RELATEDTABLE(poitem_mst)
        VAR Denom = CALCULATE(DIVIDE(SUM(poitem_mst[Line Cost]),SUM(poitem_mst[Line Qty])),FILTER(AllCal,'Calendar'[Date] = MinPODate))
        VAR Num = CALCULATE(DIVIDE(SUM(poitem_mst[Line Cost]),SUM(poitem_mst[Line Qty])),FILTER(AllCal,'Calendar'[Date] = MaxPODate))
        RETURN
       IF(OR(ISBLANK(Num),ISBLANK(Denom)),BLANK(),DIVIDE(Num-Denom,Denom))*CALCULATE([Hist_Agg.Cost],ThisPer)
    ),
    CALCULATE([Hist_Agg.Cost],ThisPer)
)

 

Regards,

Luke

Need Help

2 Replies

    • LukeWalker's avatar
      LukeWalker
      Advocate IV
      8 years ago

      That's super helpful. It looks like it's changing "Num"  when it's combined with "Denom".

       

      "RETURN Num" evaluates to X

      "RETURN Num + Denom * 0" evaluates to a value different from X

       

      I'm still clueless as to why, but now I kind of know what's going on at least.

       

      Can variables be passed between measures? There are some variable names defined in this measure (Y/Y Price Inflation) that are also used in the dependent measure (Cumulative Price Inflation). However, changing the names doesn't fix the problem either..

       

      I've also tried running the "Num" inside a CALCULATE(ALL('Calendar'),...)  clause, but that doesn't fix it.

       

      	CALCULATE([Cumulative Price Inflation],FILTER(ALL('Calendar'),'Calendar'[Date]<TODAY()))
	- CALCULATE([Cumulative Price Inflation],FILTER(ALL('Calendar'),'Calendar'[Date]<TODAY()- 365)) * 0
	=
	CALCULATE([Cumulative Price Inflation],FILTER(ALL('Calendar'),'Calendar'[Date]<TODAY())

       The above returns "TRUE". However when the two clauses are run independently, they do not equal each other.