Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)?
Solved! Go to 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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |