End of month totals based on dates around each month end
I am trying to calculate the total number of active customers at the end of each month when selecting multiple months. I am doing this by using the StartDt and DisconnectDt of their service.
So if a customer has a StartDt of 7/1/2016 and a DisconnectDt of 8/5/2016 they would show as active for Jul-2016 but not for Aug-2016. Even though they were active in August they disconnected before the last day of August.
Currently I am able to do this for the last date in my selection below. DateKey is the dates in my calendar used in month selections.
I created a future date column "DisconnectDt Closed" that has a future disconnect date, "1/1/2020", to deal with the Null value when a customer has not disconnected. Otherwise the actual disconnect date is populated.
Remember the goal is to display the total number of customers active at the end of each month. Not just the last month in my selection of months. With a Dimension of Mth-Yr the results with the data above would look like this.
I have tried different variations of Aggr() expressions with no luck.
Re: End of month totals based on dates around each month end
Thank you Manish for taking a look at this.
I think I need to find a solution that calculates this in the chart vs. in load script possibly using Aggr().
Your version works but I run into a couple of issues.
1. The application uses a binary load and takes about 60 seconds to load. At the time of this post I am at 21 minutes and counting while trying to process all of the date combinations. We have in our history over 2 million customers and this must create month end dates for each month going back 10 years or 120 months.
2. Unfortunately the way we will need to slice the data, having a single table for the account and their dates would not work in our data model.