Forum Discussion

WNT's avatar
WNT
Frequent Visitor
5 years ago
Solved

Forecast based on last know value

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?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

  • WNT's avatar
    WNT
    Frequent Visitor
    5 years ago

    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