Skip to main content
cancel
Showing results for 
Search instead 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

Reply
dekings
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

 

 

VillagesloadDateDate(+3 months)trips
umuebohia812030/09/202431/12/20240.738
umuebohia1083530/09/202431/12/20240.985
umuwosha605230/09/202431/12/20240.550
umudim1196731/03/202430/06/20241.088
umuwosha211031/03/202430/06/20240.192
umuebohia932531/03/202630/06/20260.848
umuebohia602131/12/202531/03/20260.547
alaike2347231/03/202730/06/20272.134
umuebohia1425630/06/202630/09/20261.296
umuebohia508130/06/202530/09/20250.462
umudim602830/09/202431/12/20240.548
umudim3460730/06/202430/09/20243.146
umudim302730/06/202630/09/20260.275
umuwosha50431/12/202431/03/20250.046
umuwosha174531/12/202531/03/20260.159
umuchima17562030/08/202330/11/202315.965
umuchima5847730/06/202530/09/20255.316
umuebohia6482831/08/202530/11/20255.893
umuebohia47680031/12/202531/03/202643.345
umuebohia58604831/12/202531/03/202653.277
alaike2465431/12/202531/03/20262.241
umuchima16510731/08/202430/11/202415.010
umuchima5924531/08/202430/11/20245.386
umuchima4767131/08/202430/11/20244.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

 

VillageLoadDateDate(+3 monthstripsexpected result
umuchima17562030/08/202330/11/202315.96515
umudim1196731/03/202430/06/20241.0881
umuwosha211031/03/202430/06/20240.1920
umudim3460730/06/202430/09/20243.1463
umuchima16510731/08/202430/11/202415.01015
umuchima5924531/08/202430/11/20245.3866
umuchima4767131/08/202430/11/20244.3344
umuebohia812030/09/202431/12/20240.7380
umuebohia1083530/09/202431/12/20240.9851
umuwosha605230/09/202431/12/20240.5500
umudim602830/09/202431/12/20240.5480
umuwosha50431/12/202431/03/20250.0460
umuebohia508130/06/202530/09/20250.4621
umuchima5847730/06/202530/09/20255.3166
umuebohia6482831/08/202530/11/20255.8936
umuebohia602131/12/202531/03/20260.5470
umuwosha174531/12/202531/03/20260.1590.947
umuebohia47680031/12/202531/03/202643.34543
umuebohia58604831/12/202531/03/202653.27754
alaike2465431/12/202531/03/20262.2412
umuebohia932531/03/202630/06/20260.8481
umuebohia1425630/06/202630/09/20261.2961
umudim302730/06/202630/09/20260.2751
alaike2347231/03/202730/06/20272.1342
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
v-xinruzhu-msft
Community Support
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]))
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))

 

vxinruzhumsft_0-1677032027685.png

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.

 

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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