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
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.
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
User | Count |
---|---|
118 | |
59 | |
55 | |
43 | |
41 |
User | Count |
---|---|
119 | |
66 | |
63 | |
63 | |
44 |