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