Forum Discussion

bbass82's avatar
bbass82
Icon for Helper I rankHelper I
1 year ago
Solved

Need Support - Trying to build recursive M code or DAX for POS sales projections in future weeks

Good Morning,   Really need some support here as I am at a loss for where to go next. Been a user for little under a year now and was presented with a task by my business to come up with POS sales ...
DAX or M Code List Generate
  • AlexisOlson's avatar
    AlexisOlson
    1 year ago

    It needs to be set to iterate at the right granularity. I think you may need to iterate over Material as well.

    Projected POS Units = 
SUMX (
    SUMMARIZE (
        'POS and TrendLine Merged Table',
        'POS and TrendLine Merged Table'[Customer Name],
        'POS and TrendLine Merged Table'[Material]
    ),
    VAR _MaxYear = MAX ( 'POS and TrendLine Merged Table'[Planning Year] )
    VAR _MaxWeek = MAX ( 'POS and TrendLine Merged Table'[Week Number] )
    VAR _Customer = 'POS and TrendLine Merged Table'[Customer Name]
    VAR _Material = 'POS and TrendLine Merged Table'[Material]
    VAR _AllWeeks =
        CALCULATETABLE (
            SELECTCOLUMNS ( 
                'POS and TrendLine Merged Table',
                'POS and TrendLine Merged Table'[Planning Year],
                'POS and TrendLine Merged Table'[Week Number],
                'POS and TrendLine Merged Table'[POS Units],
                'POS and TrendLine Merged Table'[Trendline Value]
            ),
            ALLSELECTED ( 'POS and TrendLine Merged Table' ),
            'POS and TrendLine Merged Table'[Customer Name]  = _Customer,
            'POS and TrendLine Merged Table'[Material]       = _Material,
            'POS and TrendLine Merged Table'[Planning Year] <= _MaxYear,
            'POS and TrendLine Merged Table'[Week Number]   <= _MaxWeek
        )
    VAR _LastUnitsRow =
        TOPN (
            1,
            FILTER ( _AllWeeks, NOT ISBLANK ( 'POS and TrendLine Merged Table'[POS Units] ) ),
            'POS and TrendLine Merged Table'[Planning Year], DESC,
            'POS and TrendLine Merged Table'[Week Number], DESC
        )
    VAR _LastUnits = MAXX ( _LastUnitsRow, 'POS and TrendLine Merged Table'[POS Units] )
    VAR _Multiplier =
        PRODUCTX (
            FILTER ( _AllWeeks, ISBLANK ( 'POS and TrendLine Merged Table'[POS Units] ) ),
            'POS and TrendLine Merged Table'[Trendline Value]
        )
    VAR _AddCols =
        ADDCOLUMNS (
            _AllWeeks,
            "ProjUnits",
                IF (
                    ISBLANK ( 'POS and TrendLine Merged Table'[POS Units] ),
                    _LastUnits * _Multiplier,
                    'POS and TrendLine Merged Table'[POS Units]
                )
        )
    VAR _Result =
        SUMX (
            FILTER (
                _AddCols,
                'POS and TrendLine Merged Table'[Planning Year] = _MaxYear &&
                'POS and TrendLine Merged Table'[Week Number]   = _MaxWeek
            ),
            [ProjUnits]
        )
    RETURN
        _Result
)