DSO countback method - MAX Date when condition is met

[VPRS]

Hi, 

I am trying to create a measure to return the MAX Date a variable is greater than or equal to another variable and ultimately return DATEDIFF for the date when the condition is met and latest selected date. 

"ReverseRT" should calculate a Reverse Running Total based on the measure "CreditSales" starting from the latest date selected and go back in time to the start of my Date series. I have filtered the report page to limit the data, but there should not be any restriction for the minimum date. 

"ReceivablesTarget" should generate the measure Total receivables for the latest available AgeingDate and remain the same in the evaluation. 

TargetDate to return the MAX date when ReverseRT is greater than or equal to ReceivablesTarget.

Result should be a DATEDIFF TargetDate vs AgeingDate.

I have tried to separate it into multiple measure but I would just like to have one measure in the end. It seems to calculate the individual variables as expected, however, when the condition is met, it is returning the latest date selected instead of the date where the condition is met. When the conditions is not met it's blank. 

I am able to generate the correct result in a SQL query for individual customers, but I want a dynamic measure to be able to see the result for total company, specific departments and customers etc. I have a separate Date table, one table for Receivables and one table for all General Ledger entries.

This is my measure: 

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 &&            

            ReverseRT>=ReceivablesTarget

        )

    )

VAR Result =

    DATEDIFF(TargetDate,AgeingDate,DAY)

RETURN

ReverseRT & " - " & ReceivablesTarget & " - " & TargetDate

[v-tsaipranay]

Hi @VPRS ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hi @VPRS ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-tsaipranay]

Hello @VPRS ,

Thank you for reaching out to the Microsoft Fabric Community Forum.

I understand that you’re looking to create a dynamic measure that calculates the TargetDate when your ReverseRT (running total of CreditSales) is greater than or equal to ReceivablesTarget, and then returns the DATEDIFF between TargetDate and AgeingDate.

Based on your requirements, I’ve simplified the DAX formula to correctly calculate the TargetDate where the condition is met, without returning the latest selected date:

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)

• TargetDate: Now dynamically returns the correct date when ReverseRT >= ReceivablesTarget, not just the latest selected date.

• DATEDIFF: Calculates the difference between TargetDate and AgeingDate.

This solution works dynamically across departments, customers, or the total company, based on report filters.

I hope this will resolve your 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.

Thankyou.

[VPRS]

Hi, 

Looks like the only adjustment made is on RETURN, TargetDate is not reflected properly in my measure and other changes would be needed.   

[v-tsaipranay]

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.

[VPRS]

No, this does not work. Not to be ungrateful, I do appreciate the input from community members, but this seems like a copy+paste from an LLM without giving it any real thought...  

[v-tsaipranay]

Hi @VPRS ,

I apologize for the inconvenience and confusion happened before.

To return a valid TargetDate when the condition is met, the dataset needs sufficient data for the reverse calculation to make sense. Otherwise, ReverseRT will not accumulate enough values to exceed the target.

Please try the below modification:

DSO_Test_Measure =
VAR AgeingDate =
    CALCULATE(
        MAX(Sheet1[Date]),
        ALLSELECTED(Sheet1[Date])
    )
VAR ReceivablesTarget = 5500  -- Your receivables target value, adjust as needed
VAR ReverseRT =
    CALCULATE(
        SUM(Sheet1[CreditSales]),
        FILTER(
            ALL(Sheet1[Date]),
            Sheet1[Date] <= AgeingDate &&
            Sheet1[Date] >= MIN(Sheet1[Date])
        )
    )
VAR TargetDate =
    CALCULATE(
        MAX(Sheet1[Date]),
        FILTER(
            ALL(Sheet1[Date]),
            ReverseRT >= ReceivablesTarget &&
            Sheet1[Date] <= AgeingDate
        )
    )
VAR Result =
    IF(
        NOT(ISBLANK(TargetDate)),
        DATEDIFF(TargetDate, AgeingDate, DAY),
        0  -- Return 0 if TargetDate is blank, otherwise the DATEDIFF value
    )
RETURN Result

I used a sample date and tried implemeting it on my end and also including the screenshot for your reference:

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.

Thankyou.

[v-tsaipranay]

Hi @VPRS ,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If my response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[bhanu_gautam]

@VPRS , Try using

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

[VPRS]

That isn't working unfortunately: