Color bar chart by 12-month trend

phuelish — Mon, 10 Sep 2018 15:59:35 GMT

Hi,

I'm using Linest_M and a date flag to calculate a 12-month trend.

Linest_m({<[Rolling 13]={1}>} aggr(
Sum({<Segment={$(=Only(pSeg_Num))},Company={$(=Only(pComp_Num))},State-={$(=Only(pExcl_State))}>} [Sales_#])
/Sum({<Segment={$(=only(pSeg_Denom))},Company={$(=vComp_Denom)},State-={$(=Only(pExcl_State))}>} [Sales_#])
,[%AsOfKey]),[%AsOfKey])*365

I'm displaying this as a KPI, but also have a vertical bar chart with showing result by month over the past 5 years.

Sum({<Segment={$(=Only(pSeg_Num))},State-={$(=Only(pExcl_State))},Company={$(=Only(pComp_Num))}>}[Sales_#])
/Sum({<Segment={$(=Only(pSeg_Denom))},State-={$(=Only(pExcl_State))},Company={$(=vComp_Denom)}>}[Sales_#])

There are two separate date fields used:

Data_Date - the month the data was generated; used as the date dimension for the line chart
Report_Month - used to filter KPIs for the month the user wants to view. This is created in the load script as =MonthName([%AsOfKey])

I created an expression to change color based on the current trend.

If(Linest_m({<[Rolling 13]={1}>} aggr(
Sum({<Segment={$(=Only(pSeg_Num))},Company={$(=Only(pComp_Num))},State-={$(=Only(pExcl_State))}>} [Sales_#])
/Sum({<Segment={$(=only(pSeg_Denom))},Company={$(=vComp_Denom)},State-={$(=Only(pExcl_State))}>} [Sales_#])
,[%AsOfKey]),[%AsOfKey])*365>0.03,'#e7aa98',if(
Linest_m({<[Rolling 13]={1}>} aggr(
Sum({<Segment={$(=Only(pSeg_Num))},Company={$(=Only(pComp_Num))},State-={$(=Only(pExcl_State))}>} [Sales_#])
/Sum({<Segment={$(=only(pSeg_Denom))},Company={$(=vComp_Denom)},State-={$(=Only(pExcl_State))}>} [Sales_#])
,[%AsOfKey]),[%AsOfKey])*365>0.0025,'#e9e9e9','#89b1c0'))

It works fine when I test it using a text box, but doesn't work when I try using it for the chart. How do I fix this?

Thanks for any ideas or suggestions you can provide.

-Frank

topic Color bar chart by 12-month trend in App Development

Color bar chart by 12-month trend