Forum Discussion
Wrong division calculation
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-p/963216
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".