Forum Discussion
Advocate IINeed help with a formula
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
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,
1 Reply
v-sihou-msftMicrosoft Employee
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,