Forecast based on last know value

[WNT]

I have a fact table with a StartDate, EndDate and amount (in this case FTE). I want to "pretend" like the end date doesn't exist and prolong the amount in the feature. See the example below of what I want to achieve (the red letters).
I have tried all kind of calculations but I don't seem to get the right result. Any suggestions?

[WNT]

@Anonymous thanks for the reply! I'll try to duplicate it in my own environement this weekend and let you know if this is the solution

[Anonymous · Accepted solution]

Hi @WNT ,

Please try the following formula to create a new table firstly:

New Table =
TOPN (
    12,
    DISTINCT (
        SELECTCOLUMNS (
            CALENDAR ( MIN ( 'Fact'[StartDatumA] ), MAX ( 'Fact'[EindDatumA] ) ),
            "YearMonth",
                YEAR ( [Date] ) * 100
                    + MONTH ( [Date] ),
            "Month", FORMAT ( [Date], "MMMM" ),
            "Value",
                LOOKUPVALUE (
                    'Table'[_D3],
                    'Table'[Maand], FORMAT ( [Date], "MMMM" ),
                    'Table'[Jaar], YEAR ( [Date] )
                )
        )
    )
)

 Then add the measure:

Measure =
IF (
    MAX ( 'Table'[Jaar] ) > YEAR ( MAX ( 'Fact'[StartDatumA] ) ),
    CALCULATE (
        MIN ( 'New Table'[Value] ),
        FILTER ( 'New Table', 'New Table'[Month] = MAX ( 'Table'[Maand] ) )
    )
)

The final output is shown below:

Here is the pbix file.

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.