Dynamically how to write this Measure !!

[anjanikumar]

Measure !!

VPY = CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-2)-CALCULATE(BUF_DATAMART_V[AMOUNT_USD_ACTUAL],BUF_DATAMART_V[CURRENT_YEAR]=YEAR(TODAY())-3)

This meaure is working but manually need to change the prior years(like -2, -3 .. etc )

Every time I dont want to change the years manually like -2 and -3, i want update dynamically that numbers( am subtracting here by using that numbers). please help me asap.

[v-yalanwu-msft · Accepted solution]

Hi, @anjanikumar ;

You could create a what-if table ,then create a measure.

1.create a what-if table

Parameter = GENERATESERIES(0, 10, 1)

2.create a measure .

VPY = 
CALCULATE (
    [MOUNT_USD_ACTUAL1],
   FILTER('Table', 'Table'[CURRENT_YEAR]
        = YEAR ( TODAY () ) - [Parameter Value]))-
CALCULATE (
    [MOUNT_USD_ACTUAL1],
   FILTER('Table', 'Table'[CURRENT_YEAR]
        = YEAR ( TODAY () ) - [Parameter Value]-1))

 The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@anjanikumar Perhaps use a parameter in a slicer and use SELECTEDVALUE in your formula? Otherwise, not really enough to go on.