Forum Discussion

VPRS's avatar
VPRS
Frequent Visitor
1 year ago

DSO countback method - MAX Date when condition is met

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 lates...
VPRS's avatar
VPRS
Frequent Visitor
1 year ago

Hi, 

 

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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    VPRS
    Frequent Visitor
    1 year ago

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

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        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.