Forum Discussion

pbi_sm's avatar
pbi_sm
New Member
2 years ago
Solved

Roll forward / duplicate values from current period into future

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...
DAX
duplicate to future periods
Rolling attrition
  • LufengYuan's avatar
    LufengYuan
    2 years ago

    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)
LufengYuan's avatar
LufengYuan
Frequent Visitor
2 years ago

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's avatar
    pbi_sm
    New Member
    2 years ago

    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.