Target based on % inc/dec last year - but trended by month

[Alpesh]

Hi, We have a target which is to decrease by 10% from previous year.

I've created my target measure which works fine and using this in the Gauge visual vs. actuals: 

Target Phone AHT =

VAR TenPc = [Ave. Phone AHT - Last Year]*0.1

Return  [Ave. Phone AHT - Last Year]-TenPc

The challenge now comes, that we need to visual this target vs. actuals per month. And be able to filter by Country. If I use the target measure in a line chart by month, the target is taking 10% per month of the previous year. Instead it should be flat (the same number) across the year. How do I make the target ignore month context as the target should be flat. 

I tried adding  ALLEXCEPT YEAR in the return statement but the figures don't seem right.

[Anonymous · Accepted solution]

Hi, @Alpesh 

Based on your description, I've created the entire sample data below:

I'm going to simulate your measure using the whole of the following: [Af. Bonn-Art-Rasteyer]

AvePhone AHT-Last Year = SUM('Table'[Ave. Phone AHT - Last Year])

Target Phone AHT:

Target Phone AHT = 
VAR TenPc = [AvePhone AHT-Last Year]*0.1
Return  [AvePhone AHT-Last Year]-TenPc

Actual measure:

EachMonth = SUM('Table'[Ave. Phone AHT - Last Year])

To keep the Target Phone AHT metric flat over the course of a year, I created a metric using the following DAX expression:

Measure = CALCULATE([Target Phone AHT],ALL('Table'[Month]))

This ignores the month context and leaves other filters such as country and year intended. Here are the results:

I have provided the PBIX file used this time below for your reference.

Best Regards

Jianpeng Li

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