cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Azat Regular Visitor
Regular Visitor

Active Clients

I have a date when client started to work with the company, and date when he finished/terminated.

I need to calculate what was total number of active clients in any given moment, First goal to see by years and then by months

I have difficulties to represent that client can be active within several months and then terminate. Usual approach stick to the one date not the range

Can you help?

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Active Clients

Hi @Azat

Create a calendar date table in Editor Queries,

Create a blank query->open advanced editor, paste the following code 

let
Source = List.Dates(#date(2018, 1, 1),365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "date"}})
in
#"Renamed Columns"

close &&apply,

create relationship between calendar table and your data table (called "Sheet1" in my test) based on "Sheet1"[start date] <->"calendar"[date]

create a measure in Sheet1,

no_count =
CALCULATE (
    COUNT ( Sheet1[client] ),
    FILTER (
        ALL ( Sheet1 ),
        [start date] <= MAX ( 'calendar'[date] )
            && [end date] >= MAX ( 'calendar'[date] )
    )
)

add "calendar"[date] in the Axis, measure in the value field

 

2.png

 

Best Regards

Maggie

 

 

 

 

10 REPLIES 10
Azat Regular Visitor
Regular Visitor

Re: Active Clients

Any ideas? or should I "decode" what I meant?

Community Support Team
Community Support Team

Re: Active Clients

Hi @Azat

For this :

"represent that client can be active within several months and then terminate"

Do you mean:

select terminate date (eg.2018/12/20), then select "3 months", you need to show the clients who are active in the period :"2018/9/20~2018/12/20"

 

Please clear me, if it is convenient, could you show me an example?

 

Best Regards

Maggie

 

Super User
Super User

Re: Active Clients

Hi,

 

Share some data and show the expected result.

Azat Regular Visitor
Regular Visitor

Re: Active Clients

@Ashish_Mathur hi I make a example above, please see file (url to dropbox)
Azat Regular Visitor
Regular Visitor

Re: Active Clients

Can any one help me? still fighting with the task
Highlighted
Azat Regular Visitor
Regular Visitor

Re: Active Clients

@v-juanli-msft

I've drown bar chart please take a look https://www.dropbox.com/s/d0tfbh0l6g6u4m4/IMG_20190104_100404.jpg?dl=0

Super User
Super User

Re: Active Clients

Hi,

 

I do not see any URL to download your Excel file.

Community Support Team
Community Support Team

Re: Active Clients

Hi @Azat

Create a calendar date table in Editor Queries,

Create a blank query->open advanced editor, paste the following code 

let
Source = List.Dates(#date(2018, 1, 1),365, #duration(1, 0, 0, 0)),
#"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "date"}})
in
#"Renamed Columns"

close &&apply,

create relationship between calendar table and your data table (called "Sheet1" in my test) based on "Sheet1"[start date] <->"calendar"[date]

create a measure in Sheet1,

no_count =
CALCULATE (
    COUNT ( Sheet1[client] ),
    FILTER (
        ALL ( Sheet1 ),
        [start date] <= MAX ( 'calendar'[date] )
            && [end date] >= MAX ( 'calendar'[date] )
    )
)

add "calendar"[date] in the Axis, measure in the value field

 

2.png

 

Best Regards

Maggie

 

 

 

 

Azat Regular Visitor
Regular Visitor

Re: Active Clients

Thank you @v-juanli-msft

 

But the result is not what I expected. Number of active clients have to grow up month to month. 
Maybe it is my bad I did not mention that some clients are active so termination column is empty so this is how we understand whether client is active or terminated

 

Capture.PNG

 

 

 

 

 

 

 

 

 

Expected result (edited)

 

Capture1.PNG