Forum Discussion
Helper INeed Support - Trying to build recursive M code or DAX for POS sales projections in future weeks
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 )
Helper IHi 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.
Super UserThis 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)