I've to measure the # of customer sales orders which are open and not shipped at the end of each month. I'm currently using an iterative loop to replicate sales order data, for each month it's open EOM, and assigning the iterative month to the new record; however, this takes 1h+ to compute and I wonder if there's a better way.
About the app ... there's 3 tables in the app (Analysis, Calendar, and Summary). The Analysis table is an island table where users can select a time period of analysis from a drop down list (PY, PYTD, YTD, etc.). The Calendar table is at the YYYY-MM level and has flags for PY, PYTD, YTD, etc. which joins a Summary table which is aggregated to YYYY-MM.
When using the KPI chart, my measure would be defined like ...
if(Analysis='YTD', Sum({<Calendar.FL_YTD={1},YearMonth=>}[NR_LINES_OUTBOUND_PIPELINE_EOM])
When presenting the data in a pivot table, the dimension would be YearMonth and measure would be Sum([NR_LINES_OUTBOUND_PIPELINE_EOM]).
The summary table has a single YearMonth field so I cannot use set analysis to associate a record to every month between the order and ship dates, if this is even possible, so it seems I've to use the iterative approach with this structure. That said, are there any inefficiencies you see in the code which could improve load time?
I've attached sample code so it's more clear ...
