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,