Measure to distribute divide a column's data by a span of time

[Anonymous]

Hi there,

I have a set of data in a quadrant visual that shows the emount of time multiple groups will spend on different activities in the next 2 weeks (I just have a filter showing the data as a relative date in the next 2 weeks). The activities are always changing, and the data includes a column for time to complete the activity, a start date, and a deadline to complete the activity.

I would love to find a measure that allows me to distribute the amount of time over the period of time between the start and end dates. For example, if I have a task that takes 60 minutes, but the start date is June 1 and the end data is June 30, the visual would show the task for 30 minutes for two 2 week stints.

Thanks for your help!

[Anonymous]

Hi. Sorry if I wasn't clear. The visual shows how much time people are going to spend on various tasks (the sum of all the time) in the next two weeks. 

As for sample data, I can't provide you with a link to the file, as my work copputer does not support that, but I have included screenshots below. What I would like to do it divide the "time spent" by the time in between "start date" and "end date". I did create a measure (timedivide), but it did not work. You will see in the sample that it is dividing time spent by 2, when it should be dividing it by 6.

Thanks for your help.

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description and sample data, I can now roughly understand your requirement, I think you can achieve this simply using a calculated column:

timedivide =

var _week=WEEKNUM([end date],2)-WEEKNUM([start date],2)

var _value=_week+1

return

DIVIDE([time spent],_value)

And you can get what you want, like this:

Then you can also create a table chart to place it like this:

You can download my test pbix file below

Thank you very much!

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-robertq-msft]

Hi, @Anonymous 

According to your description, I can not roughly understand what you want to get. Would you like to explain your example of “the visual would show the task for 30 minutes for two 2 week stints” in detail?

What’s more, would you like to post some sample data in table form or pbix file(without sensitive data) and your expected result(like the chart you want to get and the correct measure value based on your sample data)?

Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.