Help with matrix, summarize hours per week or month

I'm quite new to Power BI but very impressed by all possibilities!

I have some projectdata that I need some help with. My data consists of: (ex value)

Project (House A)

Activity (Construction)

Startdate (2020-01-10)

Stopdate (2020-01-31)

Planned hours (60.0)

Reported hours (0.0)

Rest hours (60.0)

I have created a nice Gantt chart based on the information above with good result, but now I want more 🙂

What I now want to do is to create a matrix that shows planned hours per workingday, and possible to summarize per week and month.

I have used the formula in excel networkingdays in order to calculate that it is 16 working days, giving 3.75 hours per day.

So I would like in the report power bi to find out that every day between startdate (2020-01-10) and stopdate (2020-01-31) should be calculated with 3.75 hours.

When I choose to se the data in weeks I will se week 2 = 3.75 and week 3,4 & 5 18.75 per week. total of 60 hours.

Is this possible to do without very advanced skills...

Very grateful for any assistance!

Need Help

tex628
6 years ago
Wonderful!

You can do a quick check by dragging both of the date columns from the tables to make sure that they find eachother and the relationship is working as intended:

Now this is going to seem a little strange, but we and a copy of the original projects table. So go into the query editor and make a duplicate of said table and remove any transformations untill you only have the table you started out with:

Load your queries again, go into the relationships view and create a relationship between the prject name columns in your two project tables:

Next step is to create some columns. In the Projects_2 table create the following column:
Workdays = CALCULATE(COUNTROWS(RELATEDTABLE(Projects));'Calendar'[Weekend?] = 0)
This column should use the weekend? column from the calendar to calculate to total amount of workdays in each project.

Next up we want to calculate the average amount of hours per workday with a column in Projects_2:
Hour / Workday = Projects_2[Hours] / Projects_2[Workdays]

The result should be something along the lines of this:

Finally move into the Projects table and add the following column:
Hours_2 = IF(RELATED('Calendar'[Weekend?]) = 0 ; RELATED(Projects_2[Hour / Workday]) ; BLANK())

------

Now you should have the components needed to visualize hours / workingdays / project:

I hope this is aligned with what you're aiming for!

Br,
Johannes