Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
25 | |
19 | |
18 | |
18 | |
15 |
User | Count |
---|---|
39 | |
22 | |
18 | |
15 | |
12 |