Generation of monthly targets from a table of annual targets

[Anonymous]

I have an annual targets table by state and product. 

State	Product	2020 Sales Target
California	Product 1	1,000
California	Product 2	1,000
New York	Product 1	1,000
New York	Product 2	1,000

How can I generate a monthly target table with the condition that 40% of sales should be achieved Jan - June (equally over the months), and 60% July - Dec (equally over the months)?

[Anonymous · Accepted solution]

Hello @Anonymous,

You can take the table you provided and add a custom column to it to generate the month rows by creating a list between 1 and 12.

In the Power Query editor "Custom column"

{1..12}

 Expand list.

The result should look like this:

https://gyazo.com/d254598918fc12cad7e51cdb2c286398

Next we are gonna add another column to calculate the running target values.

"Custom column"

if [MonthNum]<= 6 
then 
((0.4/6)*[MonthNum])*[2020 Sales Target] 
else 
(0.4*[2020 Sales Target]) +  ((0.6/6)*([MonthNum]-6))*[2020 Sales Target]

The result should look like this:

https://gyazo.com/9180b0453b6977002296668386ce3eb1

Kind regards
Joren Venema

Data & Analytics Consultant
If this reply solved your question be sure to mark this post as the solution to help others find the answer more easily.