running total active Customers

[DirkPuylaert]

Hello,

I'm trying to create a visual where I can see the total of Active sites based upon the selected date.

I have the following example table.

I don't just need a total, but a count of customers per month if they are between "siteActiveDate" and "siteInactiveDate"

If the "siteInactiveDate" is "-" it means the "end contractdate" is not known yet. 

Thanks in advance

customerID  customer                  siteActiveDate    siteInactiveDate

9705542	Customer 1	1/01/2017	1/05/2017
9732311	Customer 2	2/01/2017	-
2075200	Customer 3	3/01/2017	-
9675600	Customer 4	4/01/2017	-
4037701	Customer 5	5/01/2017	-
40500	Customer 6	6/01/2017	-
5613800	Customer 7	7/01/2017	-
1888800	Customer 8	8/01/2017	-
9675398	Customer 9	9/01/2017	1/09/2017
9675398	Customer 10	10/01/2017	-
9675398	Customer 11	11/01/2017	-
9705604	Customer 12	12/01/2017	-
2075200	Customer 13	13/01/2017	-
831701	Customer 14	1/02/2017	-
831701	Customer 15	2/02/2017	1/10/2017
2075200	Customer 16	3/02/2017	-
2075200	Customer 17	4/02/2017	-
9657072	Customer 10050	5/02/2017	12/02/2018

[v-jiascu-msft · Accepted solution]

Hi @DirkPuylaert,

Please check out the demo in the attachment. 

1. Create a date table if you don't have one.

2. DO NOT establish any relationship. 

3. Create a measure.

Measure =
CALCULATE (
    COUNT ( Table1[customerID] ),
    FILTER (
        'Table1',
        'Table1'[siteActiveDate] <= SELECTEDVALUE ( 'Calendar'[Date] )
            && IF (
                ISBLANK ( 'Table1'[siteInactiveDate] ),
                DATE ( 9999, 12, 31 ),
                'Table1'[siteInactiveDate]
            )
                >= SELECTEDVALUE ( 'Calendar'[Date] )
    )
)

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

Hi @DirkPuylaert,

Please check out the demo in the attachment. 

1. Create a date table if you don't have one.

2. DO NOT establish any relationship. 

3. Create a measure.

Measure =
CALCULATE (
    COUNT ( Table1[customerID] ),
    FILTER (
        'Table1',
        'Table1'[siteActiveDate] <= SELECTEDVALUE ( 'Calendar'[Date] )
            && IF (
                ISBLANK ( 'Table1'[siteInactiveDate] ),
                DATE ( 9999, 12, 31 ),
                'Table1'[siteInactiveDate]
            )
                >= SELECTEDVALUE ( 'Calendar'[Date] )
    )
)

Best Regards,
Dale

[DirkPuylaert]

Hello Dale, 

That already works for some part, thanks a lot.

Now how can I have a visual like this?

Thanks,

Dirk

[v-jiascu-msft · Accepted solution]

Hi Dirk,

Please refer to the snapshot below.

Best Regards,
Dale