Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.