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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
ang_K
Regular Visitor

Creating a planning calculator

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 Date10/6/22 
StepsStep Start DatesStart Date
110/6/22Start Date
210/6/22Start Date + 7 days
310/13/22Step 3 + 56 days
412/8/22Step 4
512/8/22Step 5 + 21 days
612/29/23Step 6
72/16/23Step 7 + 49 days
84/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!!!

1 ACCEPTED SOLUTION
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1667555249830.pngvxiaosunmsft_1-1667555264427.png

Then you can create a calculated column.

 

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

 

Final output:

vxiaosunmsft_2-1667555499119.png

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.

View solution in original post

1 REPLY 1
v-xiaosun-msft
Community Support
Community Support

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.

vxiaosunmsft_0-1667555249830.pngvxiaosunmsft_1-1667555264427.png

Then you can create a calculated column.

 

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

 

Final output:

vxiaosunmsft_2-1667555499119.png

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

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

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

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