Logic for Implementing Traffic Light

[Anonymous]

Hello All,

One query here on DAX logic to be used.

I'm trying to implement a traffic light logic to show Red/Green depending on whether the target is achieved or not. 

I have simplified the problem statement to below data. 

Target= $500 Sales in 5 years (i.e.  $100 every year)

We have 5 categories to achieve this $500 sales

eg. Cookies, Chocolates, Cakes, Pastry, and Savoury

Therefore, using equal distribution, every category should achieve $20 Sales in a year. (this would be the target).

Pretty straightforward, I can compare the actual sales w.r.t. Target ($20 in this case), and develop traffic light.

But let's say in the first year the Actual Sales was only $60 across 5 categories instead of $100. 

In that case, the target would now change to ($500-$60)= $440 in the next 4 years. 

i.e. $110 for each year. 

Therefore, category wise target would then change to $110/5 = $22 per year for a category.

How do I address this complication year on year where it would compare the actual sales and correspondingly adjust the target for the coming years. 

Note: The categories (5 to start with) are not fixed, it might change in Year 3 to either 6 or 4. But Target of $500 will be constant

Thank you 🙂

[kentyler]

So target for a given year is 500 - total sales for previous years / number of remaining years

target for a given category is target for that year/number of categories

Is that the logic you want ?

[Anonymous]

Yes, that's right.

Thanks.