topic Re: Need Support - Trying to build recursive M code or DAX for POS sales projections in future weeks in DAX Commands and Tips

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

bbass82 — Thu, 21 Aug 2025 13:42:14 GMT

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

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

Greg_Deckler — Thu, 21 Aug 2025 14:26:45 GMT

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

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

bbass82 — Thu, 21 Aug 2025 14:32:21 GMT

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.

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

AlexisOlson — Thu, 21 Aug 2025 16:06:29 GMT

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 )

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

bbass82 — Thu, 21 Aug 2025 16:53:47 GMT

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 (

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

)

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

bbass82 — Thu, 21 Aug 2025 18:06:36 GMT

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)

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

bbass82 — Thu, 21 Aug 2025 18:06:58 GMT

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

AlexisOlson — Thu, 21 Aug 2025 20:16:06 GMT

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 )

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

v-karpurapud — Mon, 25 Aug 2025 04:52:17 GMT

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.

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

v-karpurapud — Thu, 28 Aug 2025 14:10:13 GMT

Hi bbass82

We have not yet received a response regarding your query. Could you please confirm if your issue has been resolved? If not, kindly provide additional details so we can offer further assistance.

Thank you

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

v-karpurapud — Mon, 01 Sep 2025 05:15:02 GMT

Hi bbass82

We have not yet heard back from you about whether the response addressed your query. If it did not, please share more details so we can assist you more effectively.

Thank You.

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

bbass82 — Thu, 18 Sep 2025 14:16:17 GMT

So sorry all for not getting back to you till now. I fell ill and am just returning to the project. I will go through the additional solution steps provided today to see if they work as mentioned. If any issues will be sure to raise a new request for support.

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

bbass82 — Thu, 18 Sep 2025 15:09:24 GMT

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)

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

bbass82 — Thu, 18 Sep 2025 15:10:38 GMT

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 (

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

)