Need help calculating figures in a column to weekly amounts

[RichOB]

Hi,

In this scenario, I have a non-profit daycare company. The government gives me a certain amount each month to help run it, and I also receive payments that are made by the families weekly. I need to show per week the difference between these 2 amounts. 

- I need to divide the Gov_Payment by 4 and have that in the Payment_4 cell weekly, even when there are 5 weeks, E.g. September needs to be 260 for 5 weeks, not 4.

- In the system, the Gov_Payment will sometimes change throughout the year. I need this to be reflected in the Gov_Payment_4 column the week that that happens.  E.g. the change from 900 to 1040 on the 9th week.

Week_Number	Month	Family_Payment	Gov_Payment	Gov_Payment _4 (column needed)	Difference (column needed)
1	April	225	900	225	0
2	April	225		225	0
3	April	225		225	0
4	April	225		225	0
5	May	225	900	225	0
6	May	225		225	0
7	May	225		225	0
8	May	225		225	0
9	May	248	1040	260	-12
9	June	248		260	-12
10	June	248		260	-12
11	June	248		260	-12
12	June	248		260	-12
13	June	248	1040	260	-12
14	July	248		260	-12
15	July	248		260	-12
16	July	248		260	-12
17	July	248	1040	260	-12
18	August	248		260	-12
19	August	248		260	-12
20	August	248		260	-12
21	August	248	1040	260	-12
22	September	248		260	-12
23	September	248		260	-12
24	September	248		260	-12
25	September	248	1040	260	-12
26	September	248		260	-12
27	October	248		260	-12

Thanks

[bhanu_gautam · Accepted solution]

@RichOB 

1. Fill Down the Gov_Payment
In Power BI, use Power Query to fill down the Gov_Payment column:

Go to Transform Data.
Select the Gov_Payment column.
Use the Fill Down option.

Go to the Data view and add the following calculated columns:

Gov_Payment_4 = DIVIDE([Gov_Payment], 4)

Difference = [Gov_Payment_4] - [Family_Payment]

Because you filled down the Gov_Payment, any change (e.g., from 900 to 1040) will be reflected in the week it occurs and all subsequent weeks until the next change.

You specified that even in 5-week months, the weekly amount should be Gov_Payment / 4, not /5. The above logic handles this, as it always divides by 4.

[RichOB]

Hi @bhanu_gautam thanks for your reply, it solved what I was trying to do, but I realized I needed to start from a few steps back before getting to the table I input, so I had to make a new post. But thanks for this!