topic Wrong division calculation in DAX Commands and Tips

Wrong division calculation

Victormar — Thu, 23 Mar 2023 10:13:49 GMT

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! 😄

Re: Wrong division calculation

lbendlin — Fri, 24 Mar 2023 23:36:08 GMT

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-p/963216
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

Re: Wrong division calculation

Victormar — Mon, 27 Mar 2023 08:28:24 GMT

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

Re: Wrong division calculation

lbendlin — Wed, 29 Mar 2023 00:03:12 GMT

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