Forum Discussion
bbass82
Helper I
1 year agoNeed 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 ...
- 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 )
v-karpurapud
Community Support
1 year agoHi 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.
bbass82
Helper I
1 year agoSo 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.