DSO countback method - MAX Date when condition is met

Hi VPRS ,

The key issue lies in how ReverseRT is being evaluated relative to ReceivablesTarget in the TargetDate calculation. The current measure may not be correctly identifying the specific date when ReverseRT becomes greater than or equal to ReceivablesTarget, and this could be causing the issue.

To address this, I suggest the following refinements:

• Instead of directly comparing CreditSales to ReceivablesTarget, we need to properly iterate through the Date table and calculate the ReverseRT dynamically for each date. We can achieve this by refining the filter logic in the TargetDate calculation to correctly evaluate when ReverseRT exceeds or equals ReceivablesTarget.
• Also please make sure that the evaluation respects the context of your report filters (such as customer, department, etc.), so that the dynamic measure works as intended.

Here's the updated version of the DAX formula:

DSO_TEST_1Measure =
VAR AgeingDate =
    CALCULATE(
        MAX(PartyAgeing[AgeingDate]),
        ALLSELECTED('Date'[Date])
    )
VAR ReceivablesTarget =
    CALCULATE(
        [Total Receivables],
        'Date'[Date] = AgeingDate
    )
VAR ReverseRT =
    CALCULATE(
        [CreditSales],
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= AgeingDate &&
            'Date'[Date] >= MIN('Date'[Date])
        )
    )
VAR TargetDate =
    CALCULATE(
        MAX('Date'[Date]),
        FILTER(
            ALL('Date'[Date]),
            'Date'[Date] <= AgeingDate &&
            [CreditSales] >= ReceivablesTarget
        )
    )
VAR Result =
    DATEDIFF(TargetDate, AgeingDate, DAY)
RETURN
    IF(ISBLANK(TargetDate), BLANK(), Result)

• This measure calculates ReverseRT dynamically, iterating through dates until the condition is met, and ensures that TargetDate is correctly calculated as the date when the running total is greater than or equal to the receivables target.

Let me know if this adjustment resolves the issue. If you need any further assistance, feel free to reach out.

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

Thnakyou.