Reverse Running Total based On Another Column

I've got 4 columns:
date,
ContractCode,
SALES,
WIP

I'm currently struggling to calculate FirstWeekToMeetTarget.

Here’s how the FirstWeekToMeetTarget is calculated step by step:

1. Identify the Target (WIP):

   • For each row, check the value of WIP.
   • If the value is non-zero or non-blank, that row becomes the target for the calculation.

2. Reverse Cumulative Sales Calculation:

   • Starting from the current week (row with the target WIP), sum up the SALES values backward in time (week by week).
   • Continue summing until the cumulative sales meet or exceed the target value (WIP).

3. Determine the First Week:

   • The FirstWeekToMeetTarget is the earliest week (while summing backwards) where the cumulative sales first meet or exceed the target value.
   • If no week meets the target, the result will remain blank.

Here is the expected output (The dataset is a sample of one ContractCode, there are many other ContractCode):

FYI - The reason why there's no date for these two rows is becuase stakeholder has given me a sample dataset, the data for this particular ContractCode only goes back as far as 29/03/204 but once I have the full dataset, I expect the calulcation to populate these rows

Once I've got this calculated column correct I would like another calulcated column that counts the number of weeks it took SALES to >= Wip
e.g. 

This took 8 weeks of SALES to >= Wip

This took 1 week of SALES to >= @Wip

This is my current calulated table formula:

Table = 
VAR TempTable1 =
    ADDCOLUMNS(
        SUMMARIZECOLUMNS(
            'WIP & Debt Data'[Date],
            'WIP & Debt Data'[Contract Code],
            "@Sales", SUM('WIP & Debt Data'[Sales]),
            "@WIP", SUM('WIP & Debt Data'[Closing WIP & Debt])
        ),
        "CumulativeSales",
        VAR CurrentDate = [Date]
        VAR CurrentContract = [Contract Code]
        RETURN
            CALCULATE(
                SUM('WIP & Debt Data'[Sales]),
                FILTER(
                    ALL('WIP & Debt Data'),
                    'WIP & Debt Data'[Date] <= CurrentDate &&
                    'WIP & Debt Data'[Contract Code] = CurrentContract
                )
            )
    )

VAR ResultTable =
    ADDCOLUMNS(
        TempTable1,
        "FirstWeekToMeetTarget", 
VAR TargetWIPDebt = [@WIP]
VAR CurrentDate = [Date]
VAR CurrentContract = [Contract Code]
RETURN
    IF (
        TargetWIPDebt > 0,
        CALCULATE(
            MINX(
                FILTER(
                    ALLSELECTED('WIP & Debt Data'),
                    'WIP & Debt Data'[Contract Code] = CurrentContract
                    && 'WIP & Debt Data'[Date] <= CurrentDate
                ),
                'WIP & Debt Data'[Date]
            ),
            FILTER (
                ALLSELECTED ( 'WIP & Debt Data' ),
                'WIP & Debt Data'[Contract Code] = CurrentContract
                && CALCULATE (
                    SUM ( 'WIP & Debt Data'[Sales] ),
                    FILTER (
                        ALLSELECTED ( 'WIP & Debt Data' ),
                        'WIP & Debt Data'[Date] <= EARLIER ( 'WIP & Debt Data'[Date] )
                    )
                ) >= TargetWIPDebt
            )
        )
    )
    )
RETURN
    FILTER(
        ResultTable,
        NOT ISBLANK([@WIP&Debt]) // Filter out rows with blank WIP & Debt
    )

Thank you

Hi Anonymous ,

Have you solved your problem? If so, can you share your solution here and mark the correct answer as a standard answer to help other members find it faster?

Best Regards

Yilong Zhou