Solved: Re: Reverse Cumulative Sum based On Another Column

NWWill · ‎12-03-2024

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

Date	Contract Code	SALES	WIP
29/03/2024	600	1,543,706.16	10,209,064.12
05/04/2024	600	1,309,658.44	0.00
12/04/2024	600	1,628,801.66	0.00
19/04/2024	600	1,553,039.34	0.00
26/04/2024	600	1,551,622.00	8,174,078.70
03/05/2024	600	1,534,546.60	449,940.00
10/05/2024	600	1,217,576.40	0.00
17/05/2024	600	1,569,600.00	0.00
24/05/2024	600	1,498,596.52	0.00
31/05/2024	600	1,153,806.00	7,147,211.20
07/06/2024	600	1,591,384.00	1,252,049.00
14/06/2024	600	1,515,768.40	0.00
21/06/2024	600	1,362,323.98	0.00
28/06/2024	600	1,326,576.00	8,540,147.66
05/07/2024	600	1,468,997.02	0.00
12/07/2024	600	1,486,834.54	0.00
19/07/2024	600	1,463,386.62	0.00
26/07/2024	600	1,411,629.88	0.00
02/08/2024	600	1,384,225.94	10,612,779.26
09/08/2024	600	1,428,952.88	159,268.00
16/08/2024	600	1,402,028.18	0.00
23/08/2024	600	1,477,901.14	0.00
30/08/2024	600	1,129,457.16	5,413,048.61
06/09/2024	600	1,294,089.05	3,313.19
13/09/2024	600	1,271,300.55	0.00
20/09/2024	600	1,191,459.42	0.00

I'm currently struggling to calculate FirstWeekToMeetTarget.

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

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

Date	Contract Code	SALES	WIP	FirstWeekToMeetTarget
29/03/2024	600	1,543,706.16	10,209,064.12
05/04/2024	600	1,309,658.44	0.00
12/04/2024	600	1,628,801.66	0.00
19/04/2024	600	1,553,039.34	0.00
26/04/2024	600	1,551,622.00	8,174,078.70
03/05/2024	600	1,534,546.60	449,940.00	03/05/2024
10/05/2024	600	1,217,576.40	0.00
17/05/2024	600	1,569,600.00	0.00
24/05/2024	600	1,498,596.52	0.00
31/05/2024	600	1,153,806.00	7,147,211.20	26/04/2024
07/06/2024	600	1,591,384.00	1,252,049.00	07/06/2024
14/06/2024	600	1,515,768.40	0.00
21/06/2024	600	1,362,323.98	0.00
28/06/2024	600	1,326,576.00	8,540,147.66	17/05/2024
05/07/2024	600	1,468,997.02	0.00
12/07/2024	600	1,486,834.54	0.00
19/07/2024	600	1,463,386.62	0.00
26/07/2024	600	1,411,629.88	0.00
02/08/2024	600	1,384,225.94	10,612,779.26	14/06/2024
09/08/2024	600	1,428,952.88	159,268.00	09/08/2024
16/08/2024	600	1,402,028.18	0.00
23/08/2024	600	1,477,901.14	0.00
30/08/2024	600	1,129,457.16	5,413,048.61	09/08/2024
06/09/2024	600	1,294,089.05	3,313.19	06/09/2024
13/09/2024	600	1,271,300.55	0.00
20/09/2024	600	1,191,459.42	0.00

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

Date	Contract Code	SALES	WIP	FirstWeekToMeetTarget
29/03/2024	600	1,543,706.16	10,209,064.12
26/04/2024	600	1,551,622.00	8,174,078.70

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.

Date	ContractCode	SALES	WIP	FirstWeekToMeetTarget
02/08/2024	600	1,384,225.94	10,612,779.26	14/06/2024

This took 8 weeks of SALES to >= Wip

Date	ContractCode	SALES	WIP	FirstWeekToMeetTarget
06/09/2024	600	1,294,089.05	3,313.19	06/09/2024

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

lbendlin · ‎12-06-2024

FirstWeekToMeetTarget = 
var w = max('Table'[WIP])
return if(ISBLANK(w),BLANK(),
var wk = max('Table'[Date])
var a = CALCULATETABLE('Table',REMOVEFILTERS('Table'[SALES],'Table'[WIP]),'Table'[Date]<=wk)
var b = ADDCOLUMNS(a,"cm",var wkd = [Date] return sumx(filter(a,[Date]>=wkd),[SALES]))
var c = topn(1,filter(b,[cm]>=w),[Date],DESC)
return CONCATENATEX(c,[Date])
)

View solution in original post

lbendlin · ‎12-09-2024

You should be able to add that yourself. filter all week values to include the interval from the FirstWeekToMeetTarget to the current week, and return the count of rows. Minus one if you don't want to count the current week.

View solution in original post

Anonymous · ‎12-18-2024

Hi @NWWill ,

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

NWWill · ‎12-03-2024

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

Date	Contract Code	SALES	WIP
29/03/2024	600	1,543,706.16	10,209,064.12
05/04/2024	600	1,309,658.44	0.00
12/04/2024	600	1,628,801.66	0.00
19/04/2024	600	1,553,039.34	0.00
26/04/2024	600	1,551,622.00	8,174,078.70
03/05/2024	600	1,534,546.60	449,940.00
10/05/2024	600	1,217,576.40	0.00
17/05/2024	600	1,569,600.00	0.00
24/05/2024	600	1,498,596.52	0.00
31/05/2024	600	1,153,806.00	7,147,211.20
07/06/2024	600	1,591,384.00	1,252,049.00
14/06/2024	600	1,515,768.40	0.00
21/06/2024	600	1,362,323.98	0.00
28/06/2024	600	1,326,576.00	8,540,147.66
05/07/2024	600	1,468,997.02	0.00
12/07/2024	600	1,486,834.54	0.00
19/07/2024	600	1,463,386.62	0.00
26/07/2024	600	1,411,629.88	0.00
02/08/2024	600	1,384,225.94	10,612,779.26
09/08/2024	600	1,428,952.88	159,268.00
16/08/2024	600	1,402,028.18	0.00
23/08/2024	600	1,477,901.14	0.00
30/08/2024	600	1,129,457.16	5,413,048.61
06/09/2024	600	1,294,089.05	3,313.19
13/09/2024	600	1,271,300.55	0.00
20/09/2024	600	1,191,459.42	0.00

I'm currently struggling to calculate FirstWeekToMeetTarget.

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

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

Date	Contract Code	SALES	WIP	FirstWeekToMeetTarget
29/03/2024	600	1,543,706.16	10,209,064.12
05/04/2024	600	1,309,658.44	0.00
12/04/2024	600	1,628,801.66	0.00
19/04/2024	600	1,553,039.34	0.00
26/04/2024	600	1,551,622.00	8,174,078.70
03/05/2024	600	1,534,546.60	449,940.00	03/05/2024
10/05/2024	600	1,217,576.40	0.00
17/05/2024	600	1,569,600.00	0.00
24/05/2024	600	1,498,596.52	0.00
31/05/2024	600	1,153,806.00	7,147,211.20	26/04/2024
07/06/2024	600	1,591,384.00	1,252,049.00	07/06/2024
14/06/2024	600	1,515,768.40	0.00
21/06/2024	600	1,362,323.98	0.00
28/06/2024	600	1,326,576.00	8,540,147.66	17/05/2024
05/07/2024	600	1,468,997.02	0.00
12/07/2024	600	1,486,834.54	0.00
19/07/2024	600	1,463,386.62	0.00
26/07/2024	600	1,411,629.88	0.00
02/08/2024	600	1,384,225.94	10,612,779.26	14/06/2024
09/08/2024	600	1,428,952.88	159,268.00	09/08/2024
16/08/2024	600	1,402,028.18	0.00
23/08/2024	600	1,477,901.14	0.00
30/08/2024	600	1,129,457.16	5,413,048.61	09/08/2024
06/09/2024	600	1,294,089.05	3,313.19	06/09/2024
13/09/2024	600	1,271,300.55	0.00
20/09/2024	600	1,191,459.42	0.00

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

Date	Contract Code	SALES	WIP	FirstWeekToMeetTarget
29/03/2024	600	1,543,706.16	10,209,064.12
26/04/2024	600	1,551,622.00	8,174,078.70

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.

Date	ContractCode	SALES	WIP	FirstWeekToMeetTarget
02/08/2024	600	1,384,225.94	10,612,779.26	14/06/2024

This took 8 weeks of SALES to >= Wip

Date	ContractCode	SALES	WIP	FirstWeekToMeetTarget
06/09/2024	600	1,294,089.05	3,313.19	06/09/2024

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

lbendlin · ‎12-03-2024

Sales never catch up.

NWWill · ‎12-05-2024

Hi Ibendlin,

Thank you for you comment.

I think you may have missunderstood what I'm trying to achieve. To summarise and simplify what I'm trying to achieve is,

I have a table with 4 columns:

Date (each row represents one week)
Contract (let ignore this column for now)
Sales
WIP

The table is sorted by Date Asc

whenever the WIP value is greater than 0 , starting from the same row, I want to sum the Sales column going back in time till the Sales value is greater or equal to the WIP in question.

This needs to occur everytime WIP value is greater than 0.

I want the output to display how many weeks Sales took to equal or be greater than WIP

when WIP = 7,147,211.20, it took Sales (Summing backwards in time) 6 weeks to >= to WIP

when WIP = 449,940, it took Sales (summing backwards in time) 1 week to >= to WIP

and so on...

lbendlin · ‎12-06-2024

FirstWeekToMeetTarget = 
var w = max('Table'[WIP])
return if(ISBLANK(w),BLANK(),
var wk = max('Table'[Date])
var a = CALCULATETABLE('Table',REMOVEFILTERS('Table'[SALES],'Table'[WIP]),'Table'[Date]<=wk)
var b = ADDCOLUMNS(a,"cm",var wkd = [Date] return sumx(filter(a,[Date]>=wkd),[SALES]))
var c = topn(1,filter(b,[cm]>=w),[Date],DESC)
return CONCATENATEX(c,[Date])
)

NWWill · ‎12-09-2024

Thanks this worked to get the date of the FirstWeekToMeetTarget. However, instead of showing the date, I want to display how many weeks in INT it took to reach FirstWeekToMeetTarget

eg.

1. when WIP = 7,147,211.20, it took Sales (Summing backwards in time) 6 weeks to >= to WIP Output = 6

2. when WIP = 449,940, it took Sales (summing backwards in time) 1 week to >= to WIP
Output = 1

Thanks for the guidance so far!

lbendlin · ‎12-09-2024

You should be able to add that yourself. filter all week values to include the interval from the FirstWeekToMeetTarget to the current week, and return the count of rows. Minus one if you don't want to count the current week.

Reverse Running Total based On Another Column

Helpful resources

Power BI Monthly Update - May 2025

Fabric Community Update - May 2025

Enter to win a FREE Fabric certification exam voucher

Reverse Running Total based On Another Column

Helpful resources

Power BI Monthly Update - May 2025

Fabric Community Update - May 2025