How to automatic carry over the last week balance to the next week new target?

[Audit_CKGoC]

For example:

1. Each week the [target points] will be 2000 points for each employees
2. For instance, the total [actual points earned] for that week of employee is 1000 points
3. The [remaining_points] which is the difference between [target points] and [actual points] on last week which is 1000, will be carry over to the next week target points which is 2000
4. So the the next [target_points] for next week will be the [remaining_points] of last week +[target_points] of next week which is the " 3000"
5. The [remaining_points] for the next week will be based on the difference of 3000 which is the new total target points and the actual points earned for that current week.

So my question is, " How to automatic carry over the last week balance to the next week new target?"

[VGC_Innovation]

Do you have any columns identifying the week for each record? if yes, you can use use SUMIFS with such columns as part of the criteria.

[Audit_CKGoC]

I used the [week_beg] and [week_end] for last week’s date to filter the [actual_points_earned] within that date range. Is that what u mean? Thank you for responding.

week end : (TODAY() - WEEKDAY(TODAY()))
week beg: [week_end]-6

My problem is the “Automatic Carry Over” of the [remaining balance points] of last week to the [new target points] for the next week.

Thank you @VGC_Innovation.

[VGC_Innovation]

In that case you could use the following formula on column [target points]:

SUM(
Select('table name'[remaining_points],
AND([week end] = ([_thisrow].[week end]-7), [week start] = ([_thisrow].[week start]-7))
)
) + 2000

[Audit_CKGoC]

Is it okay to use the [remaining_points] which is derived column from the difference of my [target points] and [actual points]? in getting my column[target points]? Thank you @VGC_Innovation

[VGC_Innovation]

Yes, because the [remaining_points] value in the formula refers to the previous week’s record and not the current week.

[Audit_CKGoC]

Ah, the column[target points] that you mean here is the "New target points’ which is the sum of remaining_points for last week and the 2000 constant target points per week?

Yet as I used your formula, it did not come up to the result I want. Please check the screenshot of my work.

Target Week Value: 2000 (constant)per week
Actual Value Earned: 1000
Remaining: 1000
New Target Week: is where i put your suggested formula and it results to 2000. I want it to be 3000 which is the [Remaining]+2000

Thank you so much @VGC_Innovation

[VGC_Innovation]

I assumed the scenario was as illustrated above:

• There were 2 records (one for the previous week, and another for the current week)

If there were two records, the [taget_week_value] would have been updated by record 2.

[Audit_CKGoC]

Yes your assumption is correct. But this is how i present my dates. I presented it in one row only per [employee] Please see.

So my “new target week” is the same as you mean in record 2 (current week) which is column[target week value]. I don’t know how to set my formula on my last week remaining balance that it would automatically carry over to my current week’s target.

Thank you so much! @VGC_Innovation .

[VGC_Innovation]

Just to clarify, may I know what columns you are using?

[Audit_CKGoC]

Hi @VGC_Innovation. This problem is solved. Thank you for responding on my queries! You’re a big help.