cancel
Showing results for
Did you mean:
Helper I

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

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.

1 ACCEPTED SOLUTION
Community Support

Hi, @jessj

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:

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.

3 REPLIES 3
Helper I

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.

Community Support

Hi, @jessj

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:

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.

Community Support

Hi, @jessj

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!

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors