DAX formula that adds a decimal number to the next row till the end

[dekings]

Hello community, please I desperately need help.

Below is my table

Villages	load	Date	Date(+3 months)	trips
umuebohia	8120	30/09/2024	31/12/2024	0.738
umuebohia	10835	30/09/2024	31/12/2024	0.985
umuwosha	6052	30/09/2024	31/12/2024	0.550
umudim	11967	31/03/2024	30/06/2024	1.088
umuwosha	2110	31/03/2024	30/06/2024	0.192
umuebohia	9325	31/03/2026	30/06/2026	0.848
umuebohia	6021	31/12/2025	31/03/2026	0.547
alaike	23472	31/03/2027	30/06/2027	2.134
umuebohia	14256	30/06/2026	30/09/2026	1.296
umuebohia	5081	30/06/2025	30/09/2025	0.462
umudim	6028	30/09/2024	31/12/2024	0.548
umudim	34607	30/06/2024	30/09/2024	3.146
umudim	3027	30/06/2026	30/09/2026	0.275
umuwosha	504	31/12/2024	31/03/2025	0.046
umuwosha	1745	31/12/2025	31/03/2026	0.159
umuchima	175620	30/08/2023	30/11/2023	15.965
umuchima	58477	30/06/2025	30/09/2025	5.316
umuebohia	64828	31/08/2025	30/11/2025	5.893
umuebohia	476800	31/12/2025	31/03/2026	43.345
umuebohia	586048	31/12/2025	31/03/2026	53.277
alaike	24654	31/12/2025	31/03/2026	2.241
umuchima	165107	31/08/2024	30/11/2024	15.010
umuchima	59245	31/08/2024	30/11/2024	5.386
umuchima	47671	31/08/2024	30/11/2024	4.334

The trips column was derived by dividing the Load column by 11,000.

What I am looking to achieve with DAX is, return the whole number, then get the decimal multiply it by 11,000

 then add it to the next row of the Load column, 

again divide that row in Load column by 11,000 

return only the whole number and repeat the process above till end of the row.

This calculations needs to be group by the Village column and the Date  column provides Ordering 

The last row can be returned in a new row with next date (which could be in day, month or quarter)

At the end, below is the expected result. with focus on Expected result

Village	Load	Date	Date(+3 months	trips	expected result
umuchima	175620	30/08/2023	30/11/2023	15.965	15
umudim	11967	31/03/2024	30/06/2024	1.088	1
umuwosha	2110	31/03/2024	30/06/2024	0.192	0
umudim	34607	30/06/2024	30/09/2024	3.146	3
umuchima	165107	31/08/2024	30/11/2024	15.010	15
umuchima	59245	31/08/2024	30/11/2024	5.386	6
umuchima	47671	31/08/2024	30/11/2024	4.334	4
umuebohia	8120	30/09/2024	31/12/2024	0.738	0
umuebohia	10835	30/09/2024	31/12/2024	0.985	1
umuwosha	6052	30/09/2024	31/12/2024	0.550	0
umudim	6028	30/09/2024	31/12/2024	0.548	0
umuwosha	504	31/12/2024	31/03/2025	0.046	0
umuebohia	5081	30/06/2025	30/09/2025	0.462	1
umuchima	58477	30/06/2025	30/09/2025	5.316	6
umuebohia	64828	31/08/2025	30/11/2025	5.893	6
umuebohia	6021	31/12/2025	31/03/2026	0.547	0
umuwosha	1745	31/12/2025	31/03/2026	0.159	0.947
umuebohia	476800	31/12/2025	31/03/2026	43.345	43
umuebohia	586048	31/12/2025	31/03/2026	53.277	54
alaike	24654	31/12/2025	31/03/2026	2.241	2
umuebohia	9325	31/03/2026	30/06/2026	0.848	1
umuebohia	14256	30/06/2026	30/09/2026	1.296	1
umudim	3027	30/06/2026	30/09/2026	0.275	1
alaike	23472	31/03/2027	30/06/2027	2.134	2
alaike		01/04/2027			0.375
umuchima		01/07/2025			0.11
umudim		01/07/2026			0.057
umuebohia		01/07/2026			0.391

Please notice the additional rows below that retained their decimal without whole number, those were the remainder from the last rown in that particular village

[v-xinruzhu-msft]

Hi @dekings 

You can refer to the following example.

Create a new calculated column.

expected result = var _maxdate=IF(COUNTROWS(FILTER('Table (2)',[Village]=EARLIER([Village])&&[Load]=BLANK()))>0,MAXX(FILTER('Table (2)',[Village]=EARLIER([Village])&&[Load]=BLANK()),[Date]),MAXX(FILTER('Table (2)',[Village]=EARLIER([Village])),[Date]))
return IF([Date]<>_maxdate,ROUND(DIVIDE([Load],11000),0),ROUND(ROUND(DIVIDE(SUMX(FILTER('Table (2)',[Village]=EARLIER([Village])),[Load]),11000),3)-ROUNDDOWN(DIVIDE(SUMX(FILTER('Table (2)',[Village]=EARLIER([Village])),[Load]),11000),0),3))

Note:The result is rounded

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[dekings]

Thank you so much @v-xinruzhu-msft for this awesome result.
This is the closest to the desired result, but it is not exactly the result I am looking for as the decimals don't add up to the next row.
But I have been able to write a python script that solves it in Power Query. I wish I could get the same solution with DAX Power BI. It would have been better because the process with python is streneous