Forecasted Volume

[SandoPBI]

Hello,

I have 2 tables in Power BI 

1 - Actual Loan Volume which has Date and Acutal Loan Volume

2 - Pipeline Amount - Date and Incremental Pipleline Amount

I want a create a table using dax which has Date, Actual loan volume, Pipeline Amount, Forecasted Volume

The forecasted volume should take the lastest actual loan volume and add the pipeline amount. The forecast volume should contiune till the end of the month. Once the month changes, the forecasted amount should take the latest or previous forecasted volume and add the pipeline amount for the new month and continue till the end of the month, this process should continue.

If acutal volume is available the forecated vol should acutal, else it should show forecasted volume

Example of Final output should be like this

Date              Actual Vol   Pipeline Vol      Forecasted Vol

05/10/2024    13452        733                   13452

06/10/2024    13819        733                   13819 

07/10/2024                     733                     14552

.                                       733                    14552

.                                       733                    14552

.                                       733                    14552

31/10/2024                      733                   14552

01/11/2024                      133                    14685

02/11/2024                       133                   14685

.                                                                  14685        

.                                                                   14685        

30/11/2024                   133                       14685

01/12/2024                   -11                       14674

02/12/2024                   -11                       14674

.

.

31/12/2024                  -11                        14674

[Anonymous · Accepted solution]

Hi, @SandoPBI 

I am glad to help you.

Since you didn't give any specific test data, I created two table data for testing myself:

Then click New table to create a calculated table Forecasted Volume:

Forecasted Volume = 
VAR _vtable =
    SELECTCOLUMNS (
        CALENDAR (
            MIN ( MIN ( 'Actual Loan Volume'[Date] ), MIN ( 'Pipeline Amount'[Date] ) ),
            MAX ( MAX ( 'Actual Loan Volume'[Date] ), MAX ( 'Pipeline Amount'[Date] ) )
        ),
        "_Date", [Date]
    )
RETURN
    ADDCOLUMNS (
        _vtable,
        "Actual Vol",
            MAXX (
                FILTER ( 'Actual Loan Volume', 'Actual Loan Volume'[Date] = [_Date] ),
                [Actual Loan Volume]
            ),
        "Pipeline Vol",
            MAXX (
                FILTER ( 'Pipeline Amount', 'Pipeline Amount'[Date] = [_Date] ),
                'Pipeline Amount'[Incremental Pipeline Amount]
            )
    )

Finally, create a calculated column Forecasted Vol in table Forecasted Volume:

Forecasted Vol = 
	VAR _previousdate = CALCULATE(
		MAX('Forecasted Volume'[_Date]),
		FILTER(
			ALLSELECTED('Forecasted Volume'),
			'Forecasted Volume'[_Date] < EARLIER('Forecasted Volume'[_Date]) && 'Forecasted Volume'[Actual Vol] <> BLANK()
		)
	)
	VAR _vtable = SUMMARIZE(
		FILTER(
			SELECTCOLUMNS(
				'Forecasted Volume',
				[Pipeline Vol],
				"Y", YEAR([_Date]),
				"M", MONTH([_Date]),
				"_Actual Vol", MAXX(
					FILTER(
						'Forecasted Volume',
						YEAR('Forecasted Volume'[_Date]) = YEAR(EARLIER('Forecasted Volume'[_Date])) && MONTH('Forecasted Volume'[_Date]) = MONTH(EARLIER('Forecasted Volume'[_Date]))
					),
					'Forecasted Volume'[Actual Vol]
				)
			),
			[Pipeline Vol] <> BLANK()
		),
		[Y],
		[M],
		'Forecasted Volume'[Pipeline Vol],
		[_Actual Vol]
	)
	VAR _previousvalue = CALCULATE(
		MAX('Forecasted Volume'[Actual Vol]),
		FILTER(
			'Forecasted Volume',
			'Forecasted Volume'[_Date] = _previousdate
		)
	)
	VAR _vtable2 = ADDCOLUMNS(
		_vtable,
		"_SUMX", VAR _preDate = MAXX(
			FILTER(
				_vtable,
				[_Actual Vol] <> BLANK()
			),
			DATE([Y], [M], 1)
		)
		RETURN
			SUMX(
				FILTER(
					_vtable,
					[Y] >= YEAR(_preDate) && [M] > MONTH(_preDate) && [M] <= EARLIER([M])
				),
				[Pipeline Vol]
			)
	)
	RETURN
		IF(
			'Forecasted Volume'[Actual Vol] <> BLANK(),
			'Forecasted Volume'[Actual Vol],
			IF(
				MONTH('Forecasted Volume'[_Date]) = MONTH(_previousdate),
				_previousvalue,
				_previousvalue + SUMX(
					FILTER(
						_vtable2,
						YEAR('Forecasted Volume'[_Date]) = [Y] && MONTH('Forecasted Volume'[_Date]) = [M]
					),
					[_SUMX]
				)
			)
		)

I have attached the pbix file for this example below, I hope it helps!

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SandoPBI]

Thank you so much for the solution @Anonymous , sorry i didn't check the forum earlier. 

Cant thank you enough for the solution 🙏🏻

[SandoPBI]

Hi Fen,

The solution is working but needs a little bit of a tweak in terms of logic i tried to explain, may be it was clear earlier. From the output table (last table screenshot) for the date 10/09/2024 there is an Actual volume of 14500, pipeline amount of 733 and hence the forecasted vol is 14500 as the actual volume is available. However the next row where date is 10/10/2024 Actual volume is blank or zero, pipeline amount is 733, hence the forecated amount should be 14500 +733 = 15233. This amount should continue in the forecasted column till 31/10/2024. On 01/11/2024 Acutal amount is zero, pipeline amount is 133, hence the forecasted amount should be 15233+133 = 15366. This amount will continue till 30/11/2024. On 01/12/2024 Actual amount is zero, pipeline amount is -11, hence the forecasted amount should be 15366-11 = 15355

Hope I am clear now. 

[SandoPBI]

Hi Fen,

Awaiting your reply on the logic that needs a bit a tweak as explained in the earlier reply.

Regards,

SandoPBI