cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jessj
Helper I
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.

 

Thanks for your help!

1 ACCEPTED SOLUTION

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:

vrobertqmsft_0-1624331409512.png

 

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

vrobertqmsft_1-1624331409515.png

 

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.

View solution in original post

3 REPLIES 3
jessj
Helper I
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.

 

Thanks for your help.

 

jessj_0-1624287926345.png

jessj_1-1624287943878.png

jessj_2-1624287958589.png

jessj_3-1624287998398.pngjessj_4-1624288022609.png

 

 

 

 

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:

vrobertqmsft_0-1624331409512.png

 

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

vrobertqmsft_1-1624331409515.png

 

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.

View solution in original post

v-robertq-msft
Community Support
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!

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.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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