topic Re: Reverse Running Total based On Another Column in DAX Commands and Tips

Reverse Running Total based On Another Column

Anonymous — Tue, 03 Dec 2024 12:01:52 GMT

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

Re: Reverse Running Total based On Another Column

Anonymous — Tue, 03 Dec 2024 11:59:51 GMT

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

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:

Thank you

Re: Reverse Running Total based On Another Column

lbendlin — Tue, 03 Dec 2024 20:57:53 GMT

Sales never catch up.

Re: Reverse Running Total based On Another Column

Anonymous — Thu, 05 Dec 2024 09:38:08 GMT

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

Re: Reverse Running Total based On Another Column

lbendlin — Sat, 07 Dec 2024 01:57:21 GMT

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]) )

Re: Reverse Running Total based On Another Column

Anonymous — Mon, 09 Dec 2024 14:43:49 GMT

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!

Re: Reverse Running Total based On Another Column

lbendlin — Mon, 09 Dec 2024 15:51:30 GMT

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.

Re: Reverse Running Total based On Another Column

Anonymous — Thu, 19 Dec 2024 05:54:42 GMT

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