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...
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.

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.