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 siteInactiveDate9705542Customer 11/01/20171/05/20179732311Customer 22/01/2017-2075200Customer 33/01/2017-9675600Customer 44/01/2017-4037701Customer 55/01/2017-40500Customer 66/01/2017-5613800Customer 77/01/2017-1888800Customer 88/01/2017-9675398Customer 99/01/20171/09/20179675398Customer 1010/01/2017-9675398Customer 1111/01/2017-9705604Customer 1212/01/2017-2075200Customer 1313/01/2017-831701Customer 141/02/2017-831701Customer 152/02/20171/10/20172075200Customer 163/02/2017-2075200Customer 174/02/2017-9657072Customer 100505/02/201712/02/2018

DirkPuylaert

New Member

7 years ago

Solved

running total active Customers

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

7 years ago

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

running total active Customers.pbix70 KB

v-jiascu-msft
7 years ago
Hi Dirk,

Please refer to the snapshot below.

Best Regards,
Dale

3 Replies

v-jiascu-msft
Microsoft Employee
7 years ago
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
running total active Customers.pbix70 KB
- DirkPuylaert
  New Member
  7 years ago
  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
    Microsoft Employee
    7 years ago
    Hi Dirk,
    
    Please refer to the snapshot below.
    
    Best Regards,
    Dale

Forum Discussion

running total active Customers

3 Replies

Related content

Query activity runs REST API

Pipeline Run RefreshDedupedError, Run button kicking off multiple Activity Runs

Eventstreams and Activator duplicate runs

Parameterization of environment when running notebook activity

Creating ICMs via Activator