Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
5 years ago

Variable as a single measure

Hi,   I have a complex measure with some variables:     Weighted Average = VAR OneLessMonthDailySales=DIVIDE( CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-1,MONTH)), CALCULATE([...
AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
5 years ago

Once you define a variable, it's treated as a constant in the rest of the measure. The CALCULATE function changes the evaluation context but a constant is fixed regardless of evaluation context so CALCULATE doesn't do anything to a variable you've already defined. So, for example, OneLessMonthDailySales will be the same as FiveLessMonthDailySales, since shifting the dates doesn't do anything to a value that's already been determined.

  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    5 years ago

    Thanks for the respond. Now it's crlear and I will be more carefoul with that. 
    ..but it does not solve my problem to optimize the measure perfomance. What could be done instead?

    • AlexisOlson's avatar
      AlexisOlson
      Icon for Super User rankSuper User
      5 years ago

      I'm not sure if it's necessarily possible to optimize significantly. Because the context is different each time, you need to evaluate each of the prior months separately. I don't see a way to get around that since you can't actually reuse a computation.

       

      That said, you might be able to get some degree of parallelization if you write it differently. See if this works any better:

      Weighted Average =
VAR PriorMonths =
    ADDCOLUMNS (
        GENERATESERIES ( 1, 5 ),
        "MonthDailySales",
            DIVIDE (
                CALCULATE ( [Sold Prod Qnty], DATEADD ( 'Date'[Date], -[Value], MONTH ) ),
                CALCULATE ( [Unique Sales Day Count], DATEADD ( 'Date'[Date], -[Value], MONTH )
                )
            ),
        "Weight",
            ( 7 - [Value] ) / 2
    )
VAR WtdAvg =
    DIVIDE (
        SUMX ( PriorMonths, [Weight] * [MonthDailySales] ),
        SUMX ( PriorMonths, [Weight] )
    )
RETURN
    DIVIDE ( WtdAvg, [Unique Sales Day Count] )
      • George1973's avatar
        George1973
        Icon for Helper V rankHelper V
        5 years ago

        WOW, very interesting. I will try it and let you know. Thanks a lot