cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

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

2 REPLIES 2
Community Support

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]))

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.

Frequent Visitor

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors