Forum Discussion
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...
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
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)