Need help with a formula

[joschultz]

I have a matrix setup where I have columns for

Closed Date - Go Live Date - Weeks - Estimated Hours - 1/4/2016 - 1/11/2016 - 1/18/2016

 03/31/17           4/30/17         5                 80             

The close date and go live dates are data in a table.  I am calculating the weeks in a caluculated column from those other columns to get the number of weeks.  Estimated hours is also a data in the table.  I want to take the number of hours/ weeks to polulate in the matrix starting with the close data and popluating the number of weeks to the right.  So for instance I would like the number 16 to start popluating on week that 03/31/17 starts in.

Any ideas on how to do this?

Thank you,

Joseph

[v-sihou-msft · Accepted solution]

@joschultz

According to your description, you want to populate the hours/week from the "Closed Date" to "Go Live Date". Right?

In this scenario, you can just add a custom column with [Hours]/[Weeks]. Then add another column in Query Editor to generate list of dates between "Closed Date" and "Go Live Date".

= Table.AddColumn(#"Changed Type", "DatesInPeriod", each List.Dates([Closed Date],Duration.Days(Duration.From([Closed Date]-[Go Live Date])),#duration(1,0,0,0)))

After that expand the list of dates in your table, it will expant all dates into rows, and the [Hours]/[Weeks] result will be populated as well.

Regards,