Hi Maarten,

You're on the right track creating a date table to facilitate relating the 3 tables. What is the issue with regards to creating a hierarchy for year - week? Perhaps you can share an example file so we can see what you mean.

Mynda

I have 3 tables CapacityWeek, CapacityMonth and Hours (now in the sheet, normally by means of PowerQuery imported and prepared).
I created from the Hours table my DateTable and added a Week-column.
I now succeeded in creating an extra Hierarchy Year/Week.

From these tables we want to create all necessary reports which are partly on a weekly base (see Pivot Table) and others by month.
Main topic is to get someone's capacity for a specific week or month.

Can it be achieved by relationships and hierarchies or do I need to create measures?

Maarten

Hi Maarten,

Thanks for the sample file.

You need to also create the relationships between your CapacityWeek and CapacityMonth tables and the Calendar table. However, the CapacityWeek and CapacityMonth tables also need the date in them in order to create the relationships. This is because you can only have one active relationship between two tables.

If we take the example of the CapacityWeek table, you could create the relationship between the week fields, but how will you differentiate the years the data relates to? Remember, the year field in the CapacityWeek table is redundant for the purpose of creating your PivotTables because you must use the year field from the calendar table, not the CapacityWeek table.

The solution is to store your data in all fact tables (CapacityWeek, CapacityMonth and Hours) at the lowest level of data. i.e. day dates.

You can manufacture dates from the data you already have in those tables by making the month dates all the first of each month, and the week dates the first day in each week.

Once you've added these date fields to the Weeks and Months tables you can create the relationships with the Calendar table and then use the Calendar table fields in your PivotTables.

Mynda

Hi Mynda,

Thanks for these tips, I'm gonna work on that.

Kind regards,

Maarten

I managed to create the StartDates for the CapacityWeek and CapacityMonth tables,
and created Relationships to the Calender.

However, functionality for the Yera/Week-hierarchy appears to be different than the standard
(no way to 'do' something with the week), and I tried in vain to get the Capacity correctly into the week-based Pivot Table.

Any suggestions?

Maarten

Hi Maarten,

Great to see you're making progress.

As for the hierarchy issue, I can't see what the problem is. "the Year/Week-hierarchy appears to be different than the standard" what standard are you expecting exactly?

Can you please provide an example of how the data should look and where it's wrong?

Mynda

Hi Mynda,

W.r.t. the hierrachy:
in the course I did see (?) that the parts of the hierarchy can be used separately within the Pivot tables, both filter and rows. In the hoerarchy with week I cannot use Year in a filter and week as row.

What I further tried to do is relating someones Capacity to his/her working hours for the same week or month.
In the meantime I realised I needed a dimTable for the Names, and create relationships for bot Capacity and Hours.

I created the Pivot tables for Month and Week (in the same named sheets, left side), but I didn't succeed in 'just' showing the capacity in a column (as if it were a type of hour). Having that we can go further to calculate 'performance' (worked hours / (capacity - absence)).

Maarten

Hi Maarten,

I can't see any obvious issues with the first PivotTable in the Week tab. If there is something still not right with this please provide a specific example referencing the cell etc. and what you're expecting that value to be so I can find the issue.

In regards to the PivotTable on the Month sheet, you are getting the relationship warning in the PivotTable field list because you've used the Capacity field from the Month table and then expected to break it down by the HourType and AbsenceTypes in the Hours table. If you want to see Capacity by HourType and AbsenceType, you need another dimension table for these fields and you need to add these fields to the CapacityMonth table.

You also need to use the Name field from the new dim table containing the names (currently called Table1), instead of the Name from the Hours table. This change may well give you mostly what you want.

Mynda

Hi Mynda,

What I would like to do: put the Year into a filter, and the weeks in the Rows. But even dragging the Week into the Rows will only result in the whole of Hierarchy1.

About the (dim)Names: I still have to grow accustomed to get fileds from the dimTable.

I don't want to get Capacity within an hour type, but just beside it at the level of week or month. In fact we want something like this (dummy data):

To the right we then can calculate performance percentages.

Maarten

Hi Maarten,

It sounds like you shouldn't be using the hierarchy. Instead, just choose the fields from the calendar table and put them where you want, i.e. Years in Filters and Weeks in Row Labels.

The PivotTable on the Month sheet in the attached file is formatted inline with your example above, albeit with different numbers.

Mynda

Hi Mynda,

Thanks for that. Unfortunately the example in my last post wasn't fully visible:

My problem lies in the rightmost column: getting someone's capacity for that week (around 40 hrs per week) or month.
That's why I thought I needed the dataTable and the hierarchy.

Maarten

Hi Maarten,

You can get their capacity, but if you want AbsenceType and HourType in the same PivotTable then you'll have the Capacity column repeated beside the Som van Hours column. See the PivotTable in the Month sheet of the attached file.

If you want it formatted like the example image, then you'd need two separate PivotTables because PivotTables are designed to show each value field split out by the row and column labels. i.e. You can't specify the Hours to be broken out by AbsenceType and HourType, but not the capacity.

Mynda

Hi Mynda,

I'm still a bit confused.
From some examples in the Power Pivot course I got the idea that you can bring together 
data from different tables into one Pivot.
In this case: capacity is a value per week (month) and person, and I 'just' want to combine it with the hours data,
also (summarized) per week (month) and person. And 'implemented' by the relationships with Hames- and CalendarTable.

What would be the best solution then?
Merging the hours and capacity tables, some measure, or two pivots and then getting the data from it (GetPivotData)
to create a new overview?
At this moment we have several separate sheets with their own formulas to create those,
and I hoped to get all calculations (like billable hours, performance = billable / (capacity - absence) into the model.

Maarten

Hi Maarten,

You CAN have a PivotTable that shows the capacity and hours by week and person using data from multiple tables. However, you CAN'T have it also broken down by Absence Type or Hour Type because you don't have the Capacity at this level of detail.

If you want a table like the screenshot you attached earlier in this thread, then you'd need to build that with GETPIVOTDATA referencing two separate PivotTables, or use CUBE functions (see session 12). I'd use CUBE functions if I were you because they don't require the intermediate step of PivotTables.

Mynda