Simulated Weighted Average Payment Term using What-If Parameter

Hi KB88 ,

You're on the right track with your Weighted Average Payment Term (WAPT) formula, and now you want to simulate what would happen if a vendor’s payment term changes—say, if Vendor B switches entirely to 60-day terms. To do this using a What-If parameter, you'll need to override the existing payment term logic for just that vendor using the parameter value.

Start by creating a What-If parameter in Power BI. Go to Modeling > New Parameter, name it something like New Vendor PT, and set it up with values between 0 and 120 in increments of 5. This creates a new disconnected table with a column [New Vendor PT] and a measure that looks like this:

New Vendor PT Value = SELECTEDVALUE('New Vendor PT'[New Vendor PT], 60)

Now, we want to use this parameter to simulate the new payment term days—but only for Vendor B. So you’ll modify your original WAPT measure to conditionally use the parameter value when the vendor is Vendor B, and otherwise fall back to the actual related days.

Here's the full DAX measure:

SimulatedWeightedAvgPT =
VAR SimVendor = "Vendor B"
VAR SimDays = [New Vendor PT Value]
RETURN
DIVIDE(
    SUMX(
        'SC Data',
        VAR VendorName = 'SC Data'[Vendor Name]
        VAR OriginalPT = RELATED('Payment Terms'[Days])
        VAR SpendAmt = 'SC Data'[Spend]
        VAR DaysToUse =
            IF(VendorName = SimVendor, SimDays, OriginalPT)
        RETURN DaysToUse * SpendAmt
    ),
    SUM('SC Data'[Spend])
)

Drop the New Vendor PT slicer onto your report page. Now when you adjust the slicer, the measure will dynamically calculate the new weighted average assuming all of Vendor B’s transactions have the chosen term. If you'd like to simulate this for a different vendor, just replace "Vendor B" with the name of the other vendor, or make it dynamic by using a slicer and a SELECTEDVALUE() reference to the vendor name.

Best regards,

Hi KB88 ,
Thank you DataNinja777  for the helpful insights!

Upon my undesranding, I tried to recreate in on my local.Here are the steps to follow:

• Create a parameter using below, this creates a table.
  Data type: whole number Minimum: 0  Maximum: 120   and Increment: 5

Hi KB88 ,
Could you please confirm if the issue has been resolved on your end? If a solution has been found, it would be greatly appreciated if you could share your insights with the community. This would be helpful for other members who may encounter similar issues.
Thank you for your understanding and assistance.

Hi KB88 ,
I wanted to check in on your situation regarding the issue. Have you resolved it? If you have, please consider marking the reply that helped you or sharing your solution. It would be greatly appreciated by others in the community who may have the same question.
Thank you.