Data split & append

[mmoizk]

I need to do a line graph for different Resource over a period of time . The resources are working on multiple project with different start and end dates. 

My raw data is like 

Project 1 StartDate1 EndDate1 Resource1;Resource2.

The story i am trying to tell is resource are working on this many project for week1 and week2 ...Week4.

My Plan is to split that one record to 4(for now) for every week between start and end date. Here the assumption are they are 4 weeks between Start and End 

Project 1 Week1 Resource1;Resource2

Project 1 Week2 Resource1;Resource2

Project 1 Week3 Resource1;Resource2

Project 1 Week4 Resource1;Resource2

What are my best options for doing this. I was thinking of Duplicate the dataset 4 time and append all , basicaly one dataset for each week. ANd have a week column which will be If Statdate > = Today and Endate > = Today and Number of days < = 7 Then Week1 else etc....

Number of days = Days to start a project 

[ankitpatira]

@mmoizk

Can you provide snapshot of your dataset and also if you don't mind me asking what is your data source? I'll try to come up with something for you

[mmoizk]

Thanks Ankit,

My Data Source is SharePoint Online. The ask is to give a 4 week view from today for all projects that fall under a given start and End Date. If you send me your email address i can send you the same dataset. 

Project Name        Start Date           End Date           Reousce 

Project1                 05/17/2016        06/17/2016         Tom, Jerry

Project2                 04/15/2016        07/15/2016          Jerry, Henry

Project3                 06/10/2016        08/10/2016           Tom, Paul

The way i am approaching this problem is with duplicate dataset number of time i want number of weeks on X-axis. Like for 4 week view i duplicate this data 4 time and append all of it. Then calculate number of days from Start Date to Today and using that identify which week as show below. 

DaysToStart = 1*(IA_Week1_4[Impact Start Date]-TODAY())

Week = SWITCH(TRUE(),
'IA_Week1_4'[DaysToStart] < 0,"Past",
'IA_Week1_4'[DaysToStart] >= 0 && 'IA_Week1_4'[DaysToStart] <=7 ,"Week1",
'IA_Week1_4'[DaysToStart] > 7 && 'IA_Week1_4'[DaysToStart] <= 14,"Week2",
'IA_Week1_4'[DaysToStart] >14 && 'IA_Week1_4'[DaysToStart] <= 21, "Week3",
'IA_Week1_4'[DaysToStart] >21 && 'IA_Week1_4'[DaysToStart] <= 28, "Week4",
"Week4+")

In this i have to include end date too i couldn't get my head around it yet. Below is the graph i am trying to get. The lines are for resources. Below is also sample pictorial dataset  . 

[mmoizk]

Any help will be greatly appreicated . I was able to do my week1 , week2 ..... with a new columns instead of dynamic rows based on startDate and end date falling under weekly boundies. See the dataset snapshort below

Now i want to upivote or transpose these columns as rows and i am stuck. I see an unpivot option at query level but nothing at model level.

[mmoizk · Accepted solution]

So i was able to figured it out , how to do a cross join and get the desire results. First I created a static table with Week1, week2 .. with index and cross join with my Main table with needed Transpose . 

And used IA_Weekly = CROSSJOIN(IA,Week) and a conditional column to populate which week is it

Week = SWITCH('IA_Weekly'[WeekId],1,'IA_Weekly'[Week1],2,'IA_Weekly'[Week2],3,'IA_Weekly'[Week3],4,'IA_Weekly'[Week4])

[Greg_Deckler]

The only thing I can think of would be to write an "M" function that returns a table as in your second example given input of your first example, my article here might help with some of the logic as I would think that this would essentially involve some "looping" to iterate from StartDate1 to EndDate1.

[Anonymous]

I think you need a fact table that contains for every project and resouce a row with every date that’s between the starting- and end date.

My ideas ….

You can create manually the fact table:

1. Create for every project and resource a table that contains the date between starting and end
2. Add the columns "resource" and "project"
3. combine the tables and union all to a single fact table
4. add a date dimension and make a relationship between the new fact table and the date table
5. create a calculated column in your origin table where you combine the project and resource columns
6. create a calculated column in your new fact table where you combine the project and resource columns and add a relationship

Now you can create your chart.

You can try to work with parameters and a loop in M (PowerQuery) to automatize this steps ...