cancel
Showing results for
Did you mean:

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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.

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.

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}}),
in

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

Announcements

#### Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

#### Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

#### The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors