Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper IV

## 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 3
Super User

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

Helper IV

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

Super User
``````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".

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors