Set Analysis - First month of Sales by SKU and Sales by Launch Month

[emeadistributio]

Hi all, I have a question that I think might be of interester for many.

I am trying to calculate the first month where we have sales forecast, at SKU level.

The end goal is to then calculate, for each month*, the total sales (for all months) of all the SKUs that have the first month of sales on that month* - the idea is to understand when the most important product launches are (important = highest forecast for the whole forecasted period), in terms of launch month.

This without adding any new tables in the data model (if anyone has any brilliant idea on how to do this, it would be great).

I have managed to calculate the first month of forecast for each SKU, but I don't know why this works only when I filter one SKU.

The formula I used is date(Min({$<[Year-Month]={"=[Forecast]>0"}>}[Year-Month]),'YYYY-MMM'), where Year-Month is Dual.
As mentioned above, this is indeed returning the first month of forecast, but only if I select one SKU.

Data

SKU	Project	Forecast Year-Month	Forecast
1	A	Jan-22	0
1	A	Feb-22	0
1	A	Mar-22	0
1	A	Apr-22	100
1	A	May-22	100
1	...	...	...
2	A	Jan-22	...
2	A	Feb-22	200
2	...	...	...
...	...	...	...

First month of Forecast per SKU:

SKU	First month of Forecast
1	Apr-22
2	Feb-22
3	...

Ultimate Objective = Total Forecast by Launch Month

1) Assuming the following as an example (the following table is not part of the data model):

SKU	Project	First month of Forecast	Total forecast for all months
1	A	Apr-22	10.000
2	A	Feb-22	20.000
3	A	Apr-22	40.000

2) This would be the desired outcome (see pivot below - the month would be the "Forecast Month" but the calculations would show the sum (for all SKUs) of what I called above "Total forecast for all months", where "Forecast Month" = "First month of Forecast"

I would like to do this without changing the data model, but I am open to suggestions - it would be interesting to do this both with calculations only and by modifying the data model:

Project	Jan-22	Feb-22	Mar-22	Apr-22	...
A	0	20.000	0	40.000	...
B	...
C	...

[edwin]

ive seen constraints where the DM cant be modified.  but i would argue that if you plead your case maybe that condition can be waived.  this is a great candidate for DM modification.  the benefits would be scalability and maintainability.  you can come up with a briliant idea that may not work when new test cases arise or if someone else needs to maintain it.

having said that, one way would be to find the min date for each project/SKU combination. then total that by Date and project:

here is the sample data:

here is the pivot:

the expression is this:

sum(if(Project & '|' & SKU & '|' & Date=Project & '|' & SKU & '|' & aggr(nodistinct min({<Forecast={">0"}>}Date), Project,SKU), Forecast))

the AGGR gets a list of all Project/SKU/min Date where Forecast is not 0.  if the project+SKU + date = that then sum it.

[edwin]

just for curiosity, what if you throw in Project with no forecast:

[edwin]

that is if i understood the requirement correctly that you want to sum only first month forecasts

[edwin]

as this is using an IF statement and an AGGR within it, huge volumes will kill it.  the rt solution is to add a FLAG that identifies the first forecast and then you just sum where that flag = whatever.  simple, fast, easy to maintain

[vinieme12]

as below

temp:
load SKU,Project,monthname(date#([Year-Month],'MMM-YY')) as YearMonth_,Forecast

inline [
SKU,Project,Year-Month,Forecast
1,A,Jan-22,0
1,A,Feb-22,0
1,A,Mar-22,0
1,A,Apr-22,100
1,A,May-22,100
2,A,Jan-22,300
2,A,Feb-22,200
];

Measure

sum(aggr(if(YearMonth_= min({<Forecast={">0"}>}TOTAL <SKU> YearMonth_),Forecast),SKU,YearMonth_))

[emeadistributio]

Hi all, thank you very much for you help.

The requirement is to sum ALL forecast sales (of all months) on the first month where the forecast is not zero (so if we have 100 on month 1 and 300 on month 2 and 400 on month 3, we would see 800 on month 1)

what I said above is true at SKU level, while at Project level we need to see a summary like this:

- for all the SKUs of Project x starting in Jan-22 (=first month with forecast not zero is Jan-22), in Jan-22 we would see the sum of all forecast for all months for those SKU

-for all SKUs … starting in month y, in month y we souls see the sum of all forecast for all month for those SKUs

The idea is to see, for each project, when in time the most important launches are (in terms of total future forecasts of all SKUs belonging to that project being launched on each month)

[emeadistributio]

I found a solution, but I am not fully satisfied of using an if statement instead of set analysis (in the final step).

What I did:

1) added a table to the data model to calculate the Launch Month for each SKU + Country (some SKUs are shared between countries); I also calculated the last month to be considered (Launch + 11months) since the requirements actually is to calculate the forecast in the first 12 months from Launch:

[SKUlaunch]:
Load

SKU
Country,

min([Year-Month]) as [Launch Month],
addmonths(min([Year-Month]),11) as MaxMonth

RESIDENT Forecast
WHERE [Demand Forecast] > 0 //important to easily get the Launch month by calculating the "min(Year-Month)"
Group by SKU, Country;

2) Calculated the following - I tried using Set Analysis, but I could only make it work with an if statement:

sum(aggr(

if([Year-Month]<= MaxMonth,
Sum([Forecast Value]),0)

,SKU,[Year-Month],Country))

Any suggestion on how to optimize this? (using Set Analysis instead of an if statement)