Change Sales Forecast DAX for individual products

[Anonymous]

Hi Experts

I want to amend the following DAX which gives me a forecast on my sales based on the last 3year with an uplift of 5% across all products. 

I want to add and additional step into the DAX where Products (managed Services, professional Service) have a 7% and 8% uplift in sales for 2022 and sales for all other products are at 5%.

Sales Forecast =

VAR SALESLY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-1,YEAR))

VAR SALES2LY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-2,YEAR))

VAR SALES3LY = CALCULATE([Monthly Total Switch],DATEADD('Calendar'[Date],-3,YEAR))

Var Factor = 1.05

Return

DIVIDE(SALESLY+SALES2LY+SALES3LY,3,0)*Factor

[Anonymous]

Hi AMit here is the expected end result

Service	Sales	Result		Uplift
Managed Service	1	1.07		0.07
Professional Service	2	2.16		0.08
New	3	3.15		0.05
old	4	4.2		0.05
Other	5	5.25		0.05
Not only MS and PS have an uplift of 7 and 8 (%)
Everthing else 5%

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

Sales Forecast =
VAR SALESLY =
    CALCULATE( [Monthly Total Switch], DATEADD( 'Calendar'[Date], -1, YEAR ) )
VAR SALES2LY =
    CALCULATE( [Monthly Total Switch], DATEADD( 'Calendar'[Date], -2, YEAR ) )
VAR SALES3LY =
    CALCULATE( [Monthly Total Switch], DATEADD( 'Calendar'[Date], -3, YEAR ) )
VAR Factor =
    SWITCH(
        TRUE(),
        MAX( Table[service] ) = "Managed Services", 1.07,
        MAX( Table[service] ) = "Professional Services", 1.08,
        1.05
    )
RETURN
    DIVIDE( SALESLY + SALES2LY + SALES3LY, 3, 0 ) * Factor

result:

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous , Try a measure like

Var Factor = 1.05
Return

Sumx( Values(Table[Product]) ,
DIVIDE(SALESLY+SALES2LY+SALES3LY,3,0)*Switch( True() ,

max(Table[Product]) = "managed Services" , 1.07,

max(Table[Product]) = "professional Service" , 1.08,

1.05)  )

[Anonymous]

Hi Amit - getting no results back 

Measure = Sumx( Values(FACT_NEWBI_NewSalesDashboard[OrderType]) ,

[Sales Forecast]*Switch( True() ,

max(FACT_NEWBI_NewSalesDashboard[OrderType]) = "Managed Services" , 1.07,

max(FACT_NEWBI_NewSalesDashboard[OrderType]) = "Professional Services" , 1.08,

1.05) )