Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Help creating calculation for sales dashboard

Hello Everyone,

I am strugging with creating formula for catergory. I would like create YOY Catergory based on YoY Growth (%). expample , Dormant = blank, Growing = (>5%), Declining(<-5%), Flat(+/-5), New Business (previous no business and current year business) see attached screenshoot . I have added all formulas that i am using for this column. Please help me to fix this formula. It's urgent. 

I would reallt appericate with help!

 

PY_unit = CALCULATE(SUM('jde vwNetSalesandDC'[Qty Shipped]) , SAMEPERIODLASTYEAR('Calendar'[Date]))
 
CY_unit = CALCULATE(SUM('jde vwNetSalesandDC'[Qty Shipped]))
 
Growth_unit% = DIVIDE('jde vwNetSalesandDC'[CY_unit] - [PY_unit],[PY_unit])
 
YoY unit Growth = 'jde vwNetSalesandDC'[CY_unit] - [PY_unit]
 
YoY unit Growth % = divide('jde vwNetSalesandDC'[CY_unit] ,[PY_unit], 0)-1
 
YOY_CATEGORY Unit = IF( (ISBLANK('jde vwNetSalesandDC'[PY_unit] ) && ('jde vwNetSalesandDC'[CY_unit])<=0)
||
(('jde vwNetSalesandDC'[PY_unit])=0&&('jde vwNetSalesandDC'[CY_unit])=0),"DORMANT",
IF( ISBLANK('jde vwNetSalesandDC'[PY_unit])
&& NOT(ISBLANK('jde vwNetSalesandDC'[CY_unit])&&'jde vwNetSalesandDC'[CY_unit]>0), "NEW BUSINESS",
IF( 'jde vwNetSalesandDC'[Growth_unit%1] >=-0.05 &&'jde vwNetSalesandDC'[Growth_unit%1] <=0.05, "FLAT (+/- 5%)" ,
IF( 'jde vwNetSalesandDC'[Growth_unit%1] >0.05, "GROWING (>5%)",
IF('jde vwNetSalesandDC'[Growth_unit%1] <-0.05, "DECLINING (<-5%)"
)))))

 

Dashboards
Data Modeling