Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Tom Paulson]

I have the following Data:

The idea is to identify and forecast demand for receipts every month of each year. This would be column G. Obviously, we don’t know what demand will be for month (row) 1. However, we do know what the number of average yearly receipts issued is and the avergae receipts issued for each month is so we can use those figures as a starting point by taking take the AvgYearlyReceiptsIssued (All Years) and multiply it by the AvgPercentageOfReceiptsIssuedThisMonth (All Years).

For each month that follows, demand is to be determined by subtracting the expected demand of the previous month and subtracting the number of receipts issued in that previous month and adding the result to our average monthly issued receipts baseline. This process repeats for every month until the end of the table.

The below is a breakdown of the expected results for column G:

Row	Calculation	Expected Result
1	D1*E1	339478
2	(D2*E2)+(G1-F2)	52319
3	(D3*E3)+(G2-F3)	-105376
4	(D4*E4)+(G3-F4)	-163264
5	(D5*E5)+(G4-F5)	-221152
6	(D6*E6)+(G5-F6)	-55425
7	(D7*E7)+(G6-F7)	-246910

At issue, is the fact that the calculation requires column G to self-reference and iterate. My guess is that this requires using some sort of temporary table that exists inside of a loop but I have no idea how to go about this, let alone achieve the result.

Any ideas?

——————————
Tom Paulson
——————————

[Marc Schroyen]

Hi Paul,

You can’t and you don’t need to self-reference.
Formula are calculated in a context, we need to define the proper context.
Your formula 
(D2*E2)+(G1-F2) can be transformed as:
(D2*E2)+(D1*E1)-F2)
(D3*E3)+(D2*E2)+(D1*E1)-F2-F3) and so on
==> iterator
This code should work

Result = 
VAR CurrentDate = MAX( 'Table'[Start of Month] )
VAR PreviousRow = CALCULATE( MAX( 'Table'[Start of Month] ), 'Table'[Start of Month] < CurrentDate )
VAR PreviousReceiptsIssued =
	CALCULATE(
		SUM( 'Table'[Receipts issued] ),
		REMOVEFILTERS( 'Table'[Start of Month] ),
		'Table'[Start of Month] < CurrentDate
	)
VAR G =
	CALCULATE(
		SUMX(
			'Table',
			'Table'[%AvgPercentageOfReceiptsissued This Month(All Years)]
				* 'Table'[AvgYearlyReceiptsissued(All Years)]
		),
		REMOVEFILTERS( 'Table'[Start of Month] ),
		'Table'[Start of Month] <= CurrentDate
	)
RETURN
	G - PreviousReceiptsIssued?

Here is the result:
There is discrepancy with your expected result, but applying your formula in Excel gives the same result.
If I missed anything, let me know.
Checked by yourself in the pbix uploaded

ITERATOR  ITERATOR 

——————————
Marc Schroyen
Liège Belgium
——————————
——————————————-

[Tom Paulson]

Amazing! The discrepancy is caused by my rounding the E1 value in my calculations whereas your solution doesn’t. Thank you!

——————————
Tom Paulson
——————————
——————————————-