How to use group table data by start and end dates
up vote
0
down vote
favorite
After unpivoting my table using Power Query, I have this table below.
My next objective is to calculate the task allocation for each person by month in a single pivot table.
However, the start and end dates span across multiple months, how can I group the allocation by month using Excel tools like Power Query, Power Pivot, formulae etc. (anything that works)?
If I do it manually, it should look like this:
So that from this table, I can use pivot table to group the allocations by month and sum the Proportioned Allocation to find out each person's allocation per task per month.
microsoft-excel pivot-table power-query
add a comment |
up vote
0
down vote
favorite
After unpivoting my table using Power Query, I have this table below.
My next objective is to calculate the task allocation for each person by month in a single pivot table.
However, the start and end dates span across multiple months, how can I group the allocation by month using Excel tools like Power Query, Power Pivot, formulae etc. (anything that works)?
If I do it manually, it should look like this:
So that from this table, I can use pivot table to group the allocations by month and sum the Proportioned Allocation to find out each person's allocation per task per month.
microsoft-excel pivot-table power-query
add a comment |
up vote
0
down vote
favorite
up vote
0
down vote
favorite
After unpivoting my table using Power Query, I have this table below.
My next objective is to calculate the task allocation for each person by month in a single pivot table.
However, the start and end dates span across multiple months, how can I group the allocation by month using Excel tools like Power Query, Power Pivot, formulae etc. (anything that works)?
If I do it manually, it should look like this:
So that from this table, I can use pivot table to group the allocations by month and sum the Proportioned Allocation to find out each person's allocation per task per month.
microsoft-excel pivot-table power-query
After unpivoting my table using Power Query, I have this table below.
My next objective is to calculate the task allocation for each person by month in a single pivot table.
However, the start and end dates span across multiple months, how can I group the allocation by month using Excel tools like Power Query, Power Pivot, formulae etc. (anything that works)?
If I do it manually, it should look like this:
So that from this table, I can use pivot table to group the allocations by month and sum the Proportioned Allocation to find out each person's allocation per task per month.
microsoft-excel pivot-table power-query
microsoft-excel pivot-table power-query
asked Nov 13 at 15:12
Jake
73292345
73292345
add a comment |
add a comment |
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
active
oldest
votes
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fsuperuser.com%2fquestions%2f1375055%2fhow-to-use-group-table-data-by-start-and-end-dates%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown