PowerBI DAX Create Table of Hours from Project List and Start of Week Table

user_19429867 0
Viewed 39

I am trying to create a new table that contains a column with start of week and the hours estimated to be spent for that week on a per project basis.

Start of Week Project Hours
6/20/2022 ABC_XXX 10
6/27/2022 ABC_XXX 10
6/20/2022 ABC_YYY 40
6/27/2022 ABC_YYY 40

I have a table of dates representing the start of week for every project in the project table. week start date = [date]-weekday([date],2)+1

Start of Week
6/20/2022
6/27/2022
7/4/2022

The project table contains (among other things) the project name, estimated start date, duration, and hours per week.

Project Name Estimated Start Date Duration in weeks Hours Per Week
ABC_XXX 6/13/2022 8 10
ABC_YYY 6/04/2022 27 40

I am having trouble getting off the starting line. I know I need to evaluate on a per project basis and loop through all of the dates in my date table but can't find a good method to start with. I have done a lot of more simple things with creating new tables and calculations but this one is a little more complicated for me to get started. Any advice would be greatly appreciated.

The ultimate goal for this data is to present a trend showing estimated project demand over time that can be filtered by project or summed across all projects as well as filtered by timeline and displayed in a calendar view but it all starts with getting the data into this format I believe.

dax powerbi iteration union
1 Answers

Here's a Power Query solution. The steps in the code below are:

  1. use Date.AddWeeks to calculate the end date
  2. List dates between two dates
  3. Expand the list of dates and convert to date format
  4. Use Date.DayOfWeek to create a day of week column
  5. filter the table for day of week = 1 to include only weekly values (starting on Monday)

.

 let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcnRyjo+IiFDSUTLTNzTWNzIwMgKyLYDY0EApVgeiIDIyEqzABCZvZA4kTBAKoqKigALmCAVAOaAqpdhYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Project Name" = _t, #"Estimated Start Date" = _t, #"Duration in weeks" = _t, #"Hours Per Week" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Project Name", type text}, {"Estimated Start Date", type date}, {"Duration in weeks", Int64.Type}, {"Hours Per Week", Int64.Type}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Estimated End Date", each Date.AddWeeks([Estimated Start Date],[Duration in weeks])),
    #"Added Custom" = Table.AddColumn(#"Added Custom1", "dates", each {Number.From([Estimated Start Date])..Number.From([Estimated End Date])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "dates"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Expanded Custom",{{"dates", type date}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type1", "day of week", each Date.DayOfWeek([dates])),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom4", each ([day of week] = 1))
    in
        #"Filtered Rows"

result

user_133661910
Related