Forecasted Volume

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.