Roll forward / duplicate values from current period into future

[pbi_sm]

Hey there

i currently struggle with a specific problem: I have two different tables in a bigger datamodel which are connected via 1:n relationshiop. One table contains inventory data per site for different materials. The other contains forecast values. So the inventory table only has values for past and current period. The other has past, current and future. Now iw ant to create a measure or calculated column which displays values from the inventory data up to the current period and rolls forward or duplicates the most recent values into future periods. I need this for other calculations with the future periods from forecasts and other tables.

I attached a example file with a reduced version of the data model and also the screenshot below so you can understand the idea:

https://file.io/JEHTBY4rtGNW

It wont let me attach the .pbix file directly into this post - sorry.

ForecastDeliveries is from Table "Forecasts" (includes future periods) and "Summe von Amount" is from the "Inventory" table (includes only past and current periods. The calculated column or measure should behave like "Summe von Amount" but including the forward duplication of the most recent values for each combination of Period, Site and Material (green). The combination of Period_SiteID_MaterialID is also the Key which connects both tables (eg. 202312_200_100).

In my original datamodel i tried so many solutions but the values for future periods always stays blank. Hope somebody can help. Thanks in advance and kind regards.

[LufengYuan · Accepted solution]

Try the below measure ... seems to work.

Sum of Amount Test =

var _currentPeriod = MAX('Forecasts'[Period])

var _currentSite = MAX('Forecasts'[Site])

var _currentMaterial = MAX('Forecasts'[Material])

var _lastPeriod = CALCULATE(

                    MAX('Inventory'[Period]),

                    FILTER(

                    ALL('Inventory'),

                    'Inventory'[Period] <_currentPeriod &&

                    'Inventory'[Site] = _currentSite &&

                    'Inventory'[Material] = _currentMaterial

                    )

)

var _result =

CALCULATE(

    SUM('Inventory'[Amount]),

    FILTER(

        ALL('Inventory'),

    'Inventory'[Period] = _lastPeriod &&

    'Inventory'[Site] = _currentSite &&

    'Inventory'[Material]= _currentMaterial

))

RETURN IF( ISBLANK(_result), SUM('Inventory'[Amount]), _result)

[pbi_sm]

Thank you, that worked! I just changed "  'Inventory'[Period] <_currentPeriod &&" to "  'Inventory'[Period] =<_currentPeriod &&" because otherwise it would always display the values from one period before, even when looking at past periods.