Help with matrix, summarize hours per week or month

[Anonymous]

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!

[tex628]

Hello @Anonymous ,

This is actually quite advanced but i'd gladly walk you through it if you want! 

To be able to do this you will need to expand each project from a single row to a row for each day the project is active so i hope there is a managable amount of projects, otherwise you are going to have some problems!

Br,
Johannes

[Anonymous]

Hello @tex628 ,

Thanks for your quick answer.

I would be very grateful for any assistance, and in order to avoid to many project (yes I have a lot...) I will appy a filter before entering the data to powerBi.

Nevertheless I think one important question is if I have to create the daily projectrows manually? Hope not 🙂

//Lukas

[tex628]

Go into the query editor and view the proects table. 

Create a custom column that specifies the duration of each project:

Change the data format of the duration column to whole number and you should have the duration in days. 

Add a new custom column with the following code:

List.Dates([Startdate], [Duration]+1 , #duration(1, 0, 0, 0))

This should give you a list of dates for each project that you can expand, giving you the following result:


Let me know when you're done! 🙂

/ J

[Anonymous]

I think I'm done. I have used my old file so I have some more columnes, I hope that wont mess things up...

When I expand my list, I only see the dates. In your example all columns are visible. Have I done anything wrong?

[tex628]

Did you select "Expand to new rows" when you expanded? Forgot to specify! 

[Anonymous]

That was my mistake! So now I have all columns in the table! Thanks!

[tex628]

Alright!

Next step is to create a calendar to connect with the table, 

Load your queries and open the modelling tab:


I'm using the following dax code to cover all dates available in the existing projects:

Calendar = CALENDAR( MIN(Projects[Startdate]) ; MAX(Projects[Enddate]))

When you have calendar with all the dates you can add a calculated column:

Weekend? = IF(WEEKDAY('Calendar'[Date];2) > 5 ; 1 ; 0)

This should give you a column identifying all weekend days. If you have a custom calendar with holidays and such you should implement it here. All we really want to do is identify working days and non working days.

Finally you want to create a relationship between 'Calendar'[Date] and 'Project'[Dates] :

* Make sure that both columns are the same dataformat. Relationships from datetime to date doesnt work! 

When this is done all we have left is to make some calculated columns in the Projects table! 🙂

[Anonymous]

Thanks a lot @tex628 !

I am now back on track and have the calendar table with weekends etc looking forward for next steps.

[tex628 · Accepted solution]

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

[Anonymous]

Thank you very much! This what exactly what I want to create!

//Lukas

[tex628]

Of course not! We are going to create those rows. 

This is going to be quite a few steps so I'd advice you to create a copy of your current pbix in case we mess something up! 🙂 

Ill be using this small table as an example: