Bonus calculation based on number of occurences within last days, bonus should reset counting

[DaFloDo]

I am stuck with this (already simplified) problem

a day should be marked as "BONUS" if a specific event ("action 1") occurred 3 times within in a rolling time window of (the last) 5 days.

Once a day has been marked as Bonus, counting for this event should be reset/start new at the next day, so the days that are responsible for the already determined Bonus, can not "trigger" another bonus.

so only the first two columns of this example data are present in the data source, the other columns were manually added to clarify the problem:

Day	Action	Bonus	Cumulated Action within last 5 Days	Comment
01.01.2022	action 1		1
02.01.2022	action 1		2
03.01.2022			2
04.01.2022			2
05.01.2022			2
06.01.2022	action 1		2	lost 01.01.2022
07.01.2022	action 1		2	lost 02.01.2022
08.01.2022			2
09.01.2022			2
10.01.2022	action 1	Yes	3	triggers Bonus AND reset of Cumulated Action Count next day
11.01.2022	action 1		1
12.01.2022			1
13.01.2022	action 1		2
14.01.2022			2
15.01.2022	action 1	Yes	3	triggers Bonus AND reset of Cumulated Action Count next day
16.01.2022	action 1		1
17.01.2022	action 1		2
18.01.2022			2
19.01.2022			2
20.01.2022			2
21.01.2022			1
22.01.2022			0
23.01.2022			0
24.01.2022	action 1		1
25.01.2022			1
26.01.2022			1
27.01.2022			1
28.01.2022			1
29.01.2022			0
30.01.2022	action 1		1
31.01.2022	action 1		2
01.02.2022	action 1	Yes	3	triggers Bonus AND reset of Cumulated Action Count next day
02.02.2022	action 1		1
03.02.2022	action 1		2
04.02.2022	action 1	Yes	3	triggers Bonus AND reset of Cumulated Action Count next day
05.02.2022
06.02.2022	action 1		1

We were able to solve the problem with Excel, because here within a formula, you can reference the result of the same column-formula-result of a preceeding row.

I am not aware of a way in Power Query or DAX to use the same approach - using the "EARLIER" result of the calculated column you are writing the formula for seems not be supported in DAX. In Power Query I also know only to access preceeding steps or other queries, but not if/how it is possible to retrieve the calculated value of a preceeding row within the current calculation step - that was calculated in this same step.

Maybe somewone can lead me in the right direction, how to achieve the goal with Power BI possibilitis? I am out of ideas right now ...

If this simplified version can be solved, than it should be possible to solve the more complex requirement (dynamic number of days for range - i.e. 20 days, dynamic number of occurences within range that trigger bonus - i. e. 10 occurences, multiple actions)  

looking forward for any ideas

florian

[DaFloDo]

Hi @v-cazheng-msft 

thank you for your response!
Looking forward to hear from you if you find an approch using PQ, I would be more than glad!

Using DAX I had no luck using Earlier, since I could not access the result of the current calculated Column using Earlier (only other columns seem to be accessible). As you noticed, the logic depends on this value of the same "column"- so I do not see it working using Earlier.

thank you

florian

[v-cazheng-msft]

Hi @DaFloDo,

In Dax, we normally use EALIER in Calculated column or MAX in measure to get the previous row. But it’s hard to reset the counter during such kind of count. Will try to find whether it can be achieved by PQ or whether there is something i missed in DAX.

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun