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
