Hi all,
I have a application that tracks a region's top 10 sales people per month (flag created in script). An additional requirement has been added to track how any month's top 10 people can be tracked in the future. So instead of showing all sales where "FLAG.TOP_10 = 1" over n months, they want the ability to select a start date (i.e. date that determines which sales people were in the top 10 for that month) and an end date (e.g. latest month) to compare their sales to. Initial tests went well and I was able to produce a table like this (code below):
Start Date: Feb'18
End Date: Jun '18
Sales Person, Feb'18 Sales, Jun '18 Sales, Sales Movement, Still in Top 10?
John, 10.5k, 11.2k, +0.7k, Yes
Paul, 10.3k, 13.1k, +2.8k, Yes
George, 10.2k, 10.0k, -0.2k, Yes
Ringo, 9.7k, 5.2k, -4.5k, No
if(Only({$<Period=, Month=, Year=, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} Flag.Top_10) = 1 AND Flag.Top_10 = 1,
[Sales Amount (€)]
)
...etc.I'm having difficulty translating this expression into monthly trend charts that shows the Start Month followed by the 5 months preceding & including the End Date. The below script shows me the Start Date and End Date but will not show the 4 months prior to the End Date. Any thoughts on where I'm going wrong?
Sum({Alt1<Flag.Top_10 = {1}, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} [Sales Amount (€)]) // Start Month
+
// End Month & Preceding 4 months
Sum(
Aggr(
if(Only({$<Period=, Month=, Year=, [Period Date] = {'$(=Min({Alt1}[Period Date]))'}>} Flag.Top_10) = 1 AND Flag.Top_10 = 1,
Sum({$<[Period Date] = {">=$(=Max(AddMonths([Period Date],-5)))<=$(=Max([Period Date]))"}>} [Sales Amount (€)])
)
, Sales_Person_ID))
