Measure for Start and End dates - Summing Mean hours across Activities - (Events in Progress Part 2)

[draxicle1]

Dear Fabric comminuty,

This is a follow-on query regarding: Measure for Start and End Dates - Days Assigned Graph as the brief changed unexpectedly...

I would like to create a Power BI Measure that places a value (Mean Hours per Day) on every day between (and inclusive of) the start and finish dates, and then sums them across all activities for a particular period.  The result would be to plot a graph of period (x-axis) and summed hours (y-axis)

This is a simplified illustration

I hope this makes sense.

Thanks in anticipation

[draxicle1]

Please find a pbix file here, with some sample data. This is not real data, but provided so I can try to understand the concept and apply to the real world data.

https://drive.google.com/file/d/1a25dvK3rk1a6mU5T9agwehSR_QAIgftj/view?usp=drivesdk

[lbendlin]

Thank you for the sample data. In your calendar table please indicate weekends and holidays if applicable.

[draxicle1]

Hi Ibendlin,

Thanks for the file.

Whilst the table achieves the graph on this small set, I was hoping there may be a calculated measure that could achieve accumulated hours without having each date in a column. (Obviously with hundreds of activities, and multiple years. creating a table like this would be impossible. The table was just a step to help illuminate the problem).

The measure would need to somehow place the mean hours value on every day between and including the start and finish dates. 

Would it help with a sanitised excel of the real data?

Regards,

[lbendlin]

Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[draxicle1]

Forgive me, I am relatively new to PBI and would not know how to factor those into my CalendarTable.

My rationale is that holidays are not too relevant here, because teams rather than individuals are involved. Weekends are not too much of a consideration as this is for indicative purposes to show general peaks and troughs of loading of planned hours across activities, and not booked hours. The mean hours between the dates are an assumption, in any case.

My ultimate goal is to compare this to actual hours data, when I recieve it.

Would this skew the data a lot? Open to persuasion/advice.

[lbendlin]

yes, this will be rather pointless, especially with the small numbers you presented. Might be ok on a macro level (multiple years), but for your scenario you will want to consider all of this, and then some (down to business hours and timezones)

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...