LookupValue as a Calculated Measure

[Anonymous]

Hi all, 

I created calculated column where I wrote the following formula. I use the Lookup Value function with Indexes there. this formula works well, but I want to ask if you could write me how to write the same formula but for "Calculated Measures" ? Thank you

Weighted ABS Error Statistical Lag 1 last 3M = ('LAG 1 Sales History'[ABS Error Statistical Lag 1]*0.6 + (LOOKUPVALUE(

'LAG 1 Sales History'[ABS Error Statistical Lag 1],

'LAG 1 Sales History'[Index],

'LAG 1 Sales History'[Index] - 1))

*0.25 + (LOOKUPVALUE(

'LAG 1 Sales History'[ABS Error Statistical Lag 1],

'LAG 1 Sales History'[Index],

'LAG 1 Sales History'[Index] - 2))

*0.15)/1

[v-rongtiep-msft]

Hi @Anonymous ,

Please have a try.

Create a measure.

Weighted ABS Error Statistical Lag 1 last 3M =
MAX ( 'LAG 1 Sales History'[ABS Error Statistical Lag 1] ) * 0.6
    + (
        LOOKUPVALUE (
            'LAG 1 Sales History'[ABS Error Statistical Lag 1],
            'LAG 1 Sales History'[Index], 'LAG 1 Sales History'[Index] - 1
        ) * 0.25
            + (
                LOOKUPVALUE (
                    'LAG 1 Sales History'[ABS Error Statistical Lag 1],
                    'LAG 1 Sales History'[Index], 'LAG 1 Sales History'[Index] - 2
                )
            ) * 0.15
    ) / 1

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Anonymous]

A small example in Excel of what we are trying to do in PBi with the help of indexes. 

Is there any other way to achieve this? But only Calculated Measure, because Calculated Column takes a lot of memory

Thanks

[v-rongtiep-msft · Accepted solution]

Hi @Anonymous ,

Please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _3 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 2
        )
    )
VAR _2 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index]
                    = SELECTEDVALUE ( 'Table'[index] ) - 1
        )
    )
VAR _1 =
    CALCULATE (
        MAX ( 'Table'[ABS Error] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[DFU] = SELECTEDVALUE ( 'Table'[DFU] )
                && 'Table'[index] = SELECTEDVALUE ( 'Table'[index] )
        )
    )
VAR _1RE = ( ( _3 * 0.6 ) + ( _2 * 0.25 ) + ( _1 * 0.15 ) ) / 1
RETURN
    IF ( MAX ( 'Table'[index] ) <= 1, BLANK (), _1RE )

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

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

[Anonymous]

thank you, it is working now 🙂

[Anonymous]

it is not working, it´s showing this error