Show only ACTIVE Customers list

[Anonymous]

Hi,

I would like to comment an issue I am struggling with.

I have a Customer_Dim with Start_Date and End_Date by customer, and a group of Fact_Tables (Cases, Finance, etc)

Example:

Customer_Dim

This is how the data model looks like

I do not link any date from the Customer_Dim with the Calendar_Table.

The problem is that when I select a group of dates in the calendar filter to see the KPIs by customer, I get the complete list of customers. And for those customers who started after the selected dates (not existing yet), the KPI values appear in BLANK. This is confusing to the user. Something like this:

Also, the full list of customers is shown in the slicer filter. I would like to see only the “active” customers as an option in the filter and visuals.

As a 1st solution, I have created a measure to determine the Existing Customers based on the Start_Date and the MAX(DATE) of the calendar table (Blue column in the image), with the idea of filtering those “Not Existing” customers, but that makes the visual to take a long time to load – Bad user experience

As a 2nd solution I have decided to add a “YearMonth” column to the Customer_Dim to track the months where the customer is existing (“active”). In that way I can link the YearMonth_Active with the calendar table, and depending on my selected date I will always see the “Existing Customers”. Example:

But the resulting Customer_Dim table is too big, and it also makes the visuals load really slowly.

Does anyone know how I can solve this problem?

I would like that my visuals and the customer slicer to shown only the existing customers depending on the time frame I select from the calendar table.

Many thanks!

Jose

[amitchandak]

@Anonymous , refrer this file, where active is created with disjointed date table, if that can help

[Anonymous]

Hi @amitchandak 

Thanks for you answer, but this is not what I need. I do not the count of customer (Job ID in your example). What I need is to see the list of “active customers (Job ID)” when I select a specific time frame.

As you can see in your example, If I select June 2019, there are only 2 Job IDs as active, but the list in the table shows the full list from the Data Table. I would like to see the 2 Jobs IDs that are active.

The same for the filter:

Many thanks!

Jose