Forum Discussion

Victormar's avatar
Victormar
Helper V
3 years ago

Wrong division calculation

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):

c_YearsBetweenContractStartDateAndEndDate3 = (DIM_Chassis[CONTRACT_EXPIRING_DATE] - DIM_Chassis[CONTRACT_START_DATE] ) / 365. If I use YEAR() then I get 2, and I want to get decimals even if it's a year. 
 
Thanks in advance! 😄

 

3 Replies

    • Victormar's avatar
      Victormar
      Helper V
      3 years ago

      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...

      • lbendlin's avatar
        lbendlin
        Super User
        3 years ago
        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".