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
)
bbass82's avatar
bbass82
Icon for Helper I rankHelper I
1 year ago

 

AlexisOlson's avatar
AlexisOlson
Icon for Super User rankSuper User
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
)
  • bbass82's avatar
    bbass82
    Icon for Helper I rankHelper I
    11 months ago

    Hi Alexis,

     

    Not sure if you can help me with this as I adjusted my DAX as mentioned, to have the right granularity and iterrate over material as mentioned but my output looks like it is taking the last week's actuals and mutipling it by the combined trendline value in the past which is causing a return of a significantly overstated projection. 

     

    Here is what I am seeing in my table return: 

     

    Here is what I should I am trying to get as a return (based on how I would like it to calculate the return - Last week's actual x current week trendline value = projection for current week unless acutalized)

     

  • bbass82's avatar
    bbass82
    Icon for Helper I rankHelper I
    11 months ago

    This is the current DAX I have in for projected Units 

    Projected POS Units (fabric) =
    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
    )