Apply percentage decrease at specific date in forecast

[AudRab]

Hello everyone, 

I've been working on this problem for a couple of days and can't find a solution.

I have 3 tables:

• Table1 with a number of "sales" from January to December 2021 by "product"
• Table2 with "end of production date" + "consumers" (I'll get back to that later)
• Table3 with a calendar (Date, year, month, quarter, etc.)

We want to forecast the monthly sales up to 2024. So I created a forecast with Table1 data.

However, we know that sometime between 2022 and 2023 at specific quarters some of the products will no longer exist. Each product has a specific number of consumers from 1 to 4. So I created a "Switch" measure in order to decrease by X% depending on the number of consumers (1 = -100%, 2=-50%, etc.)

Here is my problem: The % decrease in the forecast needs to be applied at a specific date (quarter) mentioned in Table2. Sometimes all at once, sometimes -50% at Qn1 and -50% at Qn2, etc.

I can't seem to find a way to apply my % decrease to my forecast at the specific date mentioned in Table2.

I hope this makes sense,

Thanks a lot for your help!

[AudRab]

HI @lbendlin 

Thank you for your answer,

Below is a sample of my data:

Table1 (monthly sales by product):

Product	January	February	March	April	May	June	July	August	September	October	November	December
1	1200	2302	1573	1533	2034	1043	1745	2943	1576	1573	1352	2485
2	1344	2504	1579	1450	2943	1542	1583	1586	1632	2553	1582	2296
3	2044	2402	1733	2349	2453	1423	2953	1674	1573	3582	1573	1583

Table2 (end of production + consumers):

Product	Customers	End or production quarter
1	A	Q2 2022
1	B	Q3 2022
2	A	Q1 2023
2	C	Q3 2022
2	D	Q1 2022
3	B	Q1 2022
3	D	Q2 2022

Table3: Date table

I pivoted table 1 to have a column with 'Month' and one with 'Value'

I have a forecast measure as below:

First Month = 

FIRSTNONBLANK(

ALL(Dates[Date]), 

CALCULATE(SUM(Table1[Value])))

Last Month =

LASTNONBLANK(

ALL(Dates[Date]), 

CALCULATE(SUM(Table1[Value])))

CAGR =

VAR _LastMonth = [Last Month]

VAR _FirstMonth = [First Month]

VAR _NoMonths = [Last Month] - [First Month]

RETURN

POWER(

DIVIDE(

CALCULATE(SUM(Table1[Value], Dates[Date] = _LastMonth),

CALCULATE(SUM(Table1[Value], Dates[Date] = _FirstMonth)

), 1 / _NoMonths

) -1

Forecast=
VAR _LastMonth = [Last Month]

VAR _NoMonths = SELECTEDVALUE(Dates[Date]) - [Last Month]

RETURN

IF(SELECTEDVALUE(Dates[Date]) >= _LastMonth,

CALCULATE(SUM(Table1[Value]),

Dates[Date] = LastMonth)

* POWER(( 1 + CAGR), _NoMonths))

And I have a SWITCH measure for my %decrease based on the total on customers:

Total Customers = 

CALCULATE(COUNT( Table2[Customers]))

Decrease% = 

SWITCH([Total Customers],

1, 0,

2, 0.5,

3, 0.334,

4, 0.25,

5, 0.2,

6, 0.167)

I figured I would multiply my Value by the number associated in the SWITCH measure (based on the total of customers per product).

Now where I'm stuck is applying this decrease to the forecast at each quarter specified in Table2 under 'End of Production Quarter'

I hope this makes sense,

Thanks again for your help

[lbendlin]

You can use CROSSJOIN and INTERSECT for that scenario.  Please provide sanitized sample data that fully covers your issue. If you paste the data into a table in your post or use one of the file services it will be easier to work with. Please show the expected outcome.