Divide value by duration gives wrong result

[Anonymous]

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!

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

[v-xicai]

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])/_datediff

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.

[aj1973]

@v-xicai 

I tried your measure and it still gives out the same result,  

@Anonymous wants to see 60 instead of 61 for the id=2

[Anonymous]

@amitchandak @parry2k 

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]

Hi @Anonymous 

Change the formating of the Value_Daily from Auto to 0

[Anonymous]

@aj1973

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

[parry2k]

@Anonymous I'm actually not sure what the underline issue is, you are dividing value by days and it is returning the decimal number and then taking the sum, so what is the issue? Maybe put data for a month in an excel sheet and explain what is your expected result and what you think is wrong. Your problem statement is not clear to me

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

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-calculations/

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

[amitchandak]

@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

https://social.technet.microsoft.com/wiki/contents/articles/33644.powerbi-aggregating-durationtime-in-dax.aspx