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

[bbass82]

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 ladders that could show future week POS sales projections based on trendlines provided by our demand planners. Essentially what I am trying to build is a visual table that is by Week / Actual POS Sales (up to current calendar week) / Projected POS Sales (anything greater than current week-1). 

Below is an example of the logic I want to build for calendars weeks 1 to 52. I will have slicers also in the dashboard based on customer and material. 

want a calculated column for Projected POS Unitsthat:

• Uses actual POS if available.
• Otherwise uses the previous week's projected POS.
• Works recursively from week 1 to 52.
• Respects customer + material context.

• Confirming this is the logic I am looking to build 🧠 Example Logic (Just to Confirm)
  Let’s say you have:

  Week     Actual POS Units   Trendline
  WEEK 1           100                  1.05
  2                                             1.10
  3                                             1.08
  Then the projection would be:

  Week 2 = 100 × 1.10 = 110
  Week 3 = 110 × 1.08 = 118.8
  This is the behavior I want it to follow. 

I have attached test data based on the real subset to show what criteria I have in the merged table for POS sales and trendlines. I have also attached a copy of the M Code I was hoping would work but it is only giving a return of logic and not values (why I don't know)

Any help with this matter would be greatly appreciated. 

Customer Name	Product Family	Material	Material Description	Planning Year	Calendar Month	Week Number	POS $	POS Units	Trendline Value
Walmart	71212	6017496	Elmo Playset	2025	JAN	1	$ 1,389.60	248	1.012237
Walmart	71212	6017496	Elmo Playset	2025	JAN	2	$ 2,414.03	431	1.0001474
Walmart	71212	6017496	Elmo Playset	2025	JAN	3	$ 2,448.60	444	0.93949
Walmart	71212	6017496	Elmo Playset	2025	JAN	4	$ 2,556.30	516	1.2354279
Walmart	71212	6017496	Elmo Playset	2025	JAN	5			0.93949
Walmart	71212	6017496	Elmo Playset	2025	FEB	6			1.012237
Walmart	71212	6017496	Elmo Playset	2025	FEB	7			1.0001474
Walmart	71212	6017496	Elmo Playset	2025	FEB	8			1.2354279
Walmart	71212	6017496	Elmo Playset	2025	FEB	9			0.93949
Costco	71212	6017496	Elmo Playset	2025	JAN	1	$ 1,192.67	201	1.0001474
Costco	71212	6017496	Elmo Playset	2025	JAN	2	$ 2,313.70	404	1.012237
Costco	71212	6017496	Elmo Playset	2025	JAN	3	$ 2,906.61	502	0.93949
Costco	71212	6017496	Elmo Playset	2025	JAN	4	$ 3,426.20	609	1.2354279
Costco	71212	6017496	Elmo Playset	2025	JAN	5			0.93949
Costco	71212	6017496	Elmo Playset	2025	FEB	6			1.012237
Costco	71212	6017496	Elmo Playset	2025	FEB	7			1.024432
Costco	71212	6017496	Elmo Playset	2025	FEB	8			1.101096
Costco	71212	6017496	Elmo Playset	2025	FEB	9			0.94949

[v-karpurapud]

Hi @bbass82 

Thank you for submitting your question to the Microsoft Fabric Community Forum, and thanks to @AlexisOlson and @Greg_Deckler  for offering helpful suggestions.

Could you let us know if the suggested solution resolved your issue?If you still need help, please share more details so we can assist you further.

Thank you.

[Greg_Deckler]

@bbass82 Why not just duplicate your POS $ column and then do a Fill Down? Just make sure it is sorted correctly?

[bbass82]

Hi Greg,

Thank-you for the quick response. Sorry could you give me more detail on this possible solution? Right now our planners are updating the trednlines weekly based on new actualized POS in previous week / marketing spend changes etc. 

I am hoping to create a table visual that essentially just shows week # / actual POS sales/ Projectioned POS Sales / Cumulative (Actuals + Projected)

Week     Actual POS Units   Trendline
WEEK 1           100                  1.05
2                                             1.10
3                                             1.08
Then the projection would be:

Week 2 = 100 × 1.10 = 110
Week 3 = 110 × 1.08 = 118.8
This is the behavior I want it to follow. 

[AlexisOlson]

This doesn't actually need to be recursive. You can just multiply the trendline values and apply it to the last actual POS Units value.

Here's an ugly but working example DAX measure:

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

[bbass82]

Hi Alexis, 

Thanks so much for this. This looks to work exactly how I want to show it. I am struggling to get the out still though as I think my DAX is having a hard to iterating rows. Here is the DAX I added and the out put I received (Only rollups for completed/past weeks that only match the actuals). I have mutiple materials in my POS sales table so not sure if the filter is not working correctly. 

Projected POS Units (Fabirc) =

SUMX (

    VALUES ( 'POS and TrendLine Merged Table'[Alternate Customer Name] ),

    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'[Alternate Customer Name]

    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'[Alternate Customer Name]  = _Customer,

            '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]

I broke it down to a singular item and am now getting projections but they are coming in super high (assume summed for the material that exists from week 1 to 52 for mutiple customers) 

[bbass82]

[AlexisOlson]

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
)