Forum Discussion
Divide value by duration gives wrong result
I do have a table with a start and end date of a project and want to divide a total value by the project duration.
I am using the following column calculations:
duration = Table[end]-Table[start]
value_daily = Table[value_total]/Table[duration]
I now would like to show the values per month. When I do so, the daily value adds up and it looks like Power BI counts one more unit of [value_daily] than it should.
For instance, a project from 01/01/2020 to 12/31/2020 with a total value of 120 gives the following result:
The leap year isn't the problem, this error also occurs when the project is only in 2021.
Does anyone have an idea on that? Thanks!
9 Replies
- amitchandak
Super User
Anonymous , use datediff and then convert to duration if needed
duration = datediff(Table[start],Table[end],DAY)
Day can be replaced with other datepart
refer
- parry2k
Anonymous if you are not filtering on a year, your month will group data from all the year, for example, January will group data for January for all the year, one way is to filter data for a year or create a year/month column as described below and use that for columns.
So month will be something like Jan 2020, Jan 2021, Jan 2022, and so on.
I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!
⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡
I changed the duration calculation to the datedif measure and it is still unchanged. In addition, I was already using a date table before.
My calculations (which means the calculation of the date difference as well as the calculation for the value per day) are correct, it only looks like there is something wrong in the output.
Please check my demo file: https://we.tl/t-opboFNgFo0
I appreciate your help! Thank you!
- aj1973
Community Champion
Hi Anonymous
Change the formating of the Value_Daily from Auto to 0
Not exactly. This might be true for the first number but the second should actually be 60 and not 61.
My problem is that both the duration and the daily value are calculated correctly because when I multiply those two numbers, I get the exact value I want.
However, it does not seem to be displayed the right way in the chart.
parry2k amitchandak could you please have a look at the file I just uploaded? Thanks
v-xicaiCommunity Support
Hi Anonymous ,
You may create the [value_daily] using a measure instead of calculated column. Since the calculated column will be a fixed value, which won't be changed according to the Month or other categories except the Day.
value_daily = var _datediff= DATEDIFF(MAX(Distribution[start]),MAX(Distribution[end]), DAY) return SUM(Distribution[value])/_datediffBest Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- v-xicai
Hi Anonymous ,
From your description:
Not exactly. This might be true for the first number but the second should actually be 60 and not 61.
My problem is that both the duration and the daily value are calculated correctly because when I multiply those two numbers, I get the exact value I want.
However, it does not seem to be displayed the right way in the chart.
Could you please clarify the logic about that the second number should actually be 60 and not 61? And which is your problem on, the matrix visual or the chart visual? Note that the link in " Please check my demo file: https://we.tl/t-opboFNgFo0 " is invalid now.
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
