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

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)

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      v-juanli-msft

       

      Thank you, found out how to make it.