Number of customers over time SCD type 2

[Mlemmers]

Hi,

I've found some number of customer SCD type 2 questions but nothing quite over time. So I want to visualize the number of customers we have over time to see how our customer base is progressing. Now I only have a customer table connected to a date table. 

Think of the table as something like this (below). As you can see customer 102 left us and the rest is still with us, this is what I want to show in a line graph over time. 

SK	CustomerID	FromDate	ToDate
1	100	03/03/2020	16/11/2020
2	100	17/11/2020	04/03/2023
3	100	05/03/2023	01/01/2999
4	102	06/03/2020	06/12/2020
5	102	07/12/2020	01/01/2022
6	104	08/03/2020	01/01/2999
7	105	09/03/2020	01/01/2999
8	106	10/03/2020	01/01/2999
9	110	11/03/2020	01/01/2999

How do I count the number of customers over time like that? Is there a way to do it without having to generate every date between the start and stopdate? 

[Ashish_Mathur]

Hi,

My suggestion would be to create one row per month for each Customer (provided the lowest level of granularity you want to go to is the Month and not a specific date/date range).  Are you amenable to my approach?

[Mlemmers]

That's what I was afraid of. It generates a lot of rows over a total customer base. Is there really no other way to handle this or a way to be smarter about it? Partitioning the older states could be an idea but how do people handle when GDPR kicks in and you need to remove a customer's natural IDs from the CRM system? 

[v-yiruan-msft]

Hi @Mlemmers ,

You can create a measure as below to get it, please find the details in the attachment.

Number of customers =
VAR _seldate =
    SELECTEDVALUE ( 'Calendar'[Date] )
VAR _maxtodate =
    CALCULATE (
        MAX ( 'Table'[ToDate] ),
        ALLEXCEPT ( 'Table', 'Table'[CustomerID] )
    )
VAR _tab =
    SUMMARIZE (
        'Table',
        'Table'[CustomerID],
        'Table'[FromDate],
        "@todate",
            CALCULATE (
                MAX ( 'Table'[ToDate] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[CustomerID] = EARLIER ( 'Table'[CustomerID] )
                )
            )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( [CustomerID] ),
        FILTER ( _tab, [FromDate] <= _seldate && [@todate] >= _seldate )
    )

Best Regards

[Mlemmers]

This doesn't give me a line graph with number of customers over time. The goal is to see how the customer base develops over time in a quick glance from a line graph.