Variable as a single measure

[George1973]

Hi,

I have a complex measure with some variables:

Weighted Average = 
VAR OneLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-1,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-1,MONTH)),0)
VAR TwoLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-2,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-2,MONTH)),0)
VAR ThreeLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-3,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-3,MONTH)),0)
VAR FourLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-4,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-4,MONTH)),0)
VAR FiveLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-5,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-5,MONTH)),0)
VAR SixLessMonthDailySales=DIVIDE(
    CALCULATE([Sold Prod Qnty],DATEADD(DateKey[Date],-6,MONTH)),
    CALCULATE([Unique Sales Day Count],DATEADD(DateKey[Date],-6,MONTH)),0)
VAR WeightedAverage=
(3*OneLessMonthDailySales+2.5*TwoLessMonthDailySales+2*ThreeLessMonthDailySales+1.5*FourLessMonthDailySales+1*FiveLessMonthDailySales)/10
Return
WeightedAverage*[Unique Sales Day Count]

As you can see some outer measures have been repetedly presented in that expession such as [Sold Prod Qnty] and [Inique Sales Day Count], I would like to replace those repeted measure by single variable difining them from the very begining, like in the following examle:

Weighted Average = 
VAR SQ=[Sold Prod Qnty]
VAR SD=[Unique Sales Day Count]
VAR OneLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-1,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-1,MONTH)),0)
VAR TwoLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-2,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-2,MONTH)),0)
VAR ThreeLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-3,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-3,MONTH)),0)
VAR FourLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-4,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-4,MONTH)),0)
VAR FiveLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-5,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-5,MONTH)),0)
VAR SixLessMonthDailySales=DIVIDE(
    CALCULATE(SQ,DATEADD(DateKey[Date],-6,MONTH)),
    CALCULATE(SD,DATEADD(DateKey[Date],-6,MONTH)),0)
VAR WeightedAverage=
(3*OneLessMonthDailySales+2.5*TwoLessMonthDailySales+2*ThreeLessMonthDailySales+1.5*FourLessMonthDailySales+1*FiveLessMonthDailySales)/10
Return
WeightedAverage*SD

 As you can see the measures [Sold Prod Qnty] and [Inique Sales Day Count] are defined as starting variables "SQ" and "SD" andare used as the replacements of themseves in below given expressions. 

..But for some reasons the approach does not work! I do not undestant why.

Please help in clearing the issue.

Thanks

[Anonymous]

Hi George,

Can you try using 

IF.EAGER as in IF.EAGER your value will be computed for only one case when the tested condition is positive. In this way you can set a variable with measure value. This will improve the performace and will solve the issue as well.

[George1973]

H,
Can you provide a simple example of the "If.eager", because I have not heard about at so far 🙂

[Anonymous]

if.eager({condition that you can identify as a flag}, {Write your divide measure here}).  

[AlexisOlson]

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]

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]

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]

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