What-if Analysis with date fields

[fz1]

Hello, I have a dataset resembling the above format (~50k rows). I have the data loaded into a PowerBI dashboard with a bunch of different visuals. I am trying to build a calculator if possible to do what-if analysis, where the user will input a desired "Need to Receive By" date for a particular "Material", and a visual (for instance, a Card or Table) will spit out the "Purchase Order date" based on the average actual Lead Time for that material. For instance, if Material D needs to be ordered for a new car with a Need to Receive By date of 12/31/2024 (=user input), based on the average lead time for Material D of 5 months, the Purchase Order date (=output) should say 7/31/2024.

Is this functionality possible within PowerBI with parameters, etc.? Open to different solutions.

Thank you.

[Anonymous · Accepted solution]

Hi @fz1 ,

Please try below steps:

1. Create a date table and create a slicer with date field

2. Calculate Average Lead Time

Average Lead Time = AVERAGE('YourTable'[LeadTimeColumn])

Replace 'YourTable' and 'LeadTimeColumn' with the actual names from your dataset.

3. Create a Measure for "Purchase Order Date"

Purchase Order Date =
DATEADD(
SELECTEDVALUE('YourParameter'[DateValue]),
-1 * [Average Lead Time],
MONTH
)

This formula assumes the lead time is in months. Adjust accordingly if your lead time is in a different unit.

4. Visualize the "Purchase Order Date": Finally, use a Card or Table visual to display the "Purchase Order Date" measure. As the user adjusts the "Need to Receive By" date parameter, the visual will update to show the calculated "Purchase Order date".

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.