For example:
So my question is, " How to automatic carry over the last week balance to the next week new target?"
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.
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.
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
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
Yes, because the [remaining_points] value in the formula refers to the previous weekโs record and not the current week.
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
I assumed the scenario was as illustrated above:
If there were two records, the [taget_week_value] would have been updated by record 2.
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 .
Just to clarify, may I know what columns you are using?
Hi @VGC_Innovation. This problem is solved. Thank you for responding on my queries! Youโre a big help.
User | Count |
---|---|
36 | |
31 | |
30 | |
20 | |
17 |