Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers!
Enter the sweepstakes now!Prepping for a Fabric certification exam? Join us for a live prep session with exam experts to learn how to pass the exam. Register now.
Hello community,
It turns out that I am dividing two values, but I cannot limit the decimals on the division, then the result is not exactly correct. Any way to fix it?
3rd column is the substraction of 1st - 2nd. Then 5th column is the division between 3rd and 4th, which ideally would be 60.000, but the number really is 2,50137, and it comes from (in case it helps):
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
Hi @lbendlin , sure I can post it 😄
Bus | Contract_Start_Date | Contract_End_Date | Years | Years (I want) |
1 | 2017-05-04 | 2027-05-03 | 10,00274 | 10 |
2 | 2018-12-01 | 2031-11-30 | 13,00548 | 13 |
3 | 2019-03-03 | 2024-12-15 | 5,79178 | 5,79 |
4 | 2019-04-02 | 2027-04-01 | 8,00274 | 8 |
5 | 2019-08-02 | 2029-08-01 | 10,00548 | 10 |
6 | 2022-06-16 | 2024-12-15 | 2,50137 | 2,50 |
My calculation for the year is: (Contract_End_Date - Contract_Start_Date) / 365
The problem is I don't get exact numbers, I can get 2,50137 instead of 2,5, and I can obviously limit the display of that number to only 2 decimals and it looks good, but when calculating the total mileage per year, is looks like:
Bus | Total_Contract_Mileage | Years_of_Contract_Calculated | Years_of_Contract (I want) | Yearly_Mileage (Calculated) | Yearly_Mileage (I want) |
1 | 750000 | 10,00274 | 10 | 75021 | 75000 |
2 | 780000 | 13,00548 | 13 | 60008 | 60000 |
And so on...
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc7RCcAwCATQXfyu4BmTprOE7r9GTQ+ENn9HnqdrCeQQN5xqXS3e4Awtw9ktn9zHEiecClfjVIMC2mzDWbARXtnAkmyMPYWeIbxgFAw1r9XB+hEFe8FZkAHfGwf/8sChGP/VDsL7AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Bus = _t, Contract_Start_Date = _t, Contract_End_Date = _t, #"Contract Mileage" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Contract_Start_Date", type date}, {"Contract_End_Date", type date}, {"Contract Mileage", Int64.Type}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Years", each Number.Round(Number.From([Contract_End_Date]-[Contract_Start_Date])/365),Int64.Type),
#"Added Custom1" = Table.AddColumn(#"Added Custom", "Yearly Mileage", each [Contract Mileage]/[Years])
in
#"Added Custom1"
How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
17 | |
15 | |
13 | |
10 | |
10 |