Creating a planning calculator

[ang_K]

We have a tool in Excel that has a list of steps in a project plan. The user can enter a start date for the project and all the Start Dates for the steps recalculate (the Start Dates have formulas in them calculating the appropriate amount of days from a given previous step). A few of the Start Dates can be adjusted manually and subsequent steps' Start Dates adjust accordingly (although the user can leave the Start Dates as they are, accepting the default calculation).

Here's sort of an example (the rows in red indicate dates the user can adjust manually, with subsequent dates calculating based on manual adjustments):

Start Date	10/6/22
Steps	Step Start Dates	Start Date
1	10/6/22	Start Date
2	10/6/22	Start Date + 7 days
3	10/13/22	Step 3 + 56 days
4	12/8/22	Step 4
5	12/8/22	Step 5 + 21 days
6	12/29/23	Step 6
7	2/16/23	Step 7 + 49 days
8	4/27/23	= Step 8 + 70 days

Obviously, Excel has limitations so I've been tasked with moving this to PowerBI. My background is SQL and I'm just getting my feet wet in Power BI. I could easily create a calculator like this using a stored procedures and paginated reports, creating parameters for the manual dates. I can also create this in M using parameters, but I do not seem to have the ability to bind parameters in Power BI back to power query parameters (after googling this, it appears to be a new feature that some have found to be missing). I'm not sure that's the route to go because if a user were to change a date, they would want to see the subsequent dates recalculate dynamically, without refreshing.

So I think I need to find a solution using DAX. I'm working on using What-If parameters for various the manual dates (the live calculator will have about 12 of them). I know I can display the selected value of a slicer in a table measure. This is where I'm stuck. I need to end up with a table that is a union of both the static calculated dates and dynamic. Each step has a number activities and roles associated with it, so the table will be used in several visuals beyond the basic example I present.

In SQL, I have a parameterized stored procedure that calculates each step's start dates (if parameter is null, then use default calc) and unions them as rows to create a table. Is there a better approach in DAX? Is what I need to do possible?

Thanks in advance for any suggestions!!!

[v-xiaosun-msft · Accepted solution]

Hi @ang_K ,

According to your description, I suggest that you can use "split columns" in Power Query to transform the table as the screenshot shows.

Then you can create a calculated column.

Column = IF('Table'[Merged]=BLANK(),'Table'[Step Start Dates],'Table'[Step Start Dates]+'Table'[Merged])

Final output:

About the steps of "split columns", please reference the applied steps in my sample.

Split columns by delimiter - Power Query | Microsoft Learn

I attach my sample below for your reference.

Best Regards,
Community Support Team _ xiaosun

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.