Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
mmoizk
Helper III
Helper III

Data split & append

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 

1 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 . WeekTable.JPG

 

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])

View solution in original post

6 REPLIES 6
ankitpatira
Community Champion
Community Champion

@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

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  . 

WeekOnX-axis.JPGSampleDataSet.JPG

 

 

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

Transpose.JPG

 

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.

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 . WeekTable.JPG

 

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
Super User
Super User

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.

 

 


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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 ...

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.