How to Allocate monthly plan differently each week?

[Anonymous]

Hello,

I want to allocate/distribute monthly plan into weeks or days differently. Our sales always go dramatically high at the beginning of the month and very low at the end of the month. So our Performance goes to like 70 or 80 percent in the first half of the month and it is kind of giving us some problems. it is looking like this

 

What I want to do is to allocate/distribute the first half moth with a higher target than the second half. 

for example, right now we are doing this:  target/workingday

so I would like to allocate the target like this : 

week1: 40%

week2: 30%

week3: 20%

wekk4: 10%

meaning our monthly target is 100 USD. we must make 40 USD in the first week, 30 USD in the 2nd week, and so on. 

Hope I made it clean. Please let me know if you need any questions. 

I am hoping some people already do this thing. I am thinking I just don't know the name of this term or method.

I tried to google but I couldn't find it. That is why I am posting here. Thank you all

Edit:

because we sell higher in the first half of the month we see something like 80% of the target is achieved in the first half of the month and at the end of the month we cant even make it to 100%. So I would like to make a little smart monthly plan that allocates a higher percentage of the target in the first half of the month. 
Let's say our target is 100 and we sold 80 in the first half of the month. and how we see is 80/100=80% of the target hit (just a simple one that everyone does). Instead of showing 80%. I would like to show it like 50% or 60% because we always sell higher in the first half of the month. 

Do you understand what I am saying? I am not even sure that there is such calculation out there. So I am wondering if anyone has this type of calculation or there is already a method for this so I would like to read further if anyone knows they keyword or name for these kinds of methods. 

I am sorry If I am making this complicated. I am having a hard time explaining this clearly. 

this chart is just to show how our sales go throughout the month.

this is the link to Power Bi file. =>  The link to PowerBi file 

[v-lili6-msft · Accepted solution]

hi @Anonymous 

You may try this way as below:

Step1:

Create a year-month column

YearMonth = YEAR([Date])*100+MONTH([Date])

Step2:

Create a new quantity column by this logic as below:

New Quantity = var rate=SWITCH('Table'[Weeks],
"Week 1",0.4,
"Week 2",0.1,
"Week 3",0.3,
"Week 4",0.2) 
return
IF('Table'[Weekdays]<=5,CALCULATE(SUM('Table'[ Quantity ]),FILTER('Table','Table'[YearMonth]=EARLIER('Table'[YearMonth])))*rate/5)

or

New Quantity 2 = var rate=SWITCH('Table'[Weeks],
"Week 1",0.4,
"Week 2",0.1,
"Week 3",0.3,
"Week 4",0.2) 
return
IF('Table'[Weekdays]<=5,CALCULATE(SUM('Table'[ Quantity ]),FILTER('Table','Table'[YearMonth]=EARLIER('Table'[YearMonth])))*rate*DIVIDE('Table'[ Quantity ],CALCULATE(SUM('Table'[ Quantity ]),FILTER('Table','Table'[YearMonth]=EARLIER('Table'[YearMonth])&&'Table'[Weeks]=EARLIER('Table'[Weeks])))))

Now use this new quantity column in the report instead of old one

Result:

 

and here is sample pbix file, please try it.

Regards,

Lin

[Anonymous]

Woaw. this is exactly what I was hoping to do. Thank you very much! 

[Anonymous]

@Anonymous  thank you for your effort. I have checked them out and learnt new things. but I couldn't really see how I can adopt this. 

@v-lili6-msft  Ok. I added something to the main post (Original post). Hope I made myself clear this time. And made up a pbix file. Sorry for an unclear post. 

Please let me know if you have any question. I would be more than happy to answer. 

[v-lili6-msft]

hi  @Anonymous 

You may add a IF column to allocate monthly plan differently each week, please share your sample pbix file and your expected output, that will be a great help.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Regards,

Lin

[Anonymous]

this is a good example of what you are trying to do 

https://www.absentdata.com/power-bi/parameter-power-bi/

[Anonymous]

i would create a seperate table with the % of budget then use that to calculate the result 

if you want to make it dynamic then you need to create a series of parameters that you can change for each week 

https://www.youtube.com/watch?v=iiNDq2VrZPY here is a quick video 

let me know if this makes sense