Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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 b...
General Comment
Need Help
Show and Tell
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous

    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

     

     

    Best Regards

    Maggie

     

     

     

     

v-juanli-msft's avatar
v-juanli-msft
Community Support
7 years ago

Hi Anonymous

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

 

    • v-juanli-msft's avatar
      v-juanli-msft
      Community Support
      7 years ago

      Hi Anonymous

      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

       

       

      Best Regards

      Maggie

       

       

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        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

         

         

         

         

         

         

         

         

         

         

        Expected result (edited)