Forum Discussion

erasm_w's avatar
erasm_w
Frequent Visitor
6 years ago
Solved

Active Clients over time

Hi Forum,

 

I have a table with Customer Id and start date and whent he Customer de-activate and end date gets assigned otherwise if there is not end date they are still considered active

Cust_ID     Start_Dt            End Dt

01             01/02/2019

02             03/05/2019      

03             03/05/2019       16/01/2020

04             04//06/2019       20/02/2020

 

I would like to see number of active customers per month over time.

 

Any suggestions how to do this calculation for a line or bar graph?

 

Thanks

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi erasm_w ,

    First, please make sure there is one date dimension table in your model just like the table "Calendar" in below screen shot. Then create a measure to get active clients, and create a line chart with field "Date" in date dimension table as Axis field and new measure as "Values" field.

    Active customer = 
VAR _curdate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Customer'[Cust_ID ] ),
        FILTER (
            'Customer',
            'Customer'[Start_Dt] < _curdate
                && OR ( ISBLANK ( 'Customer'[End_Dt] ), 'Customer'[End_Dt] > _curdate )
        )
    )

    Best Regards

    Rena

3 Replies

  • jdbuchanan71's avatar
    jdbuchanan71
    Icon for Super User rankSuper User
    6 years ago

    Hello erasm_w 

    Something along these lines should work for you.

    Customer Count = 
VAR _MinDate = FIRSTDATE(DATES[Date])
VAR _MaxDate = LASTDATE(DATES[Date])
RETURN
CALCULATE(
    DISTINCTCOUNT('Table'[Cust_ID]),
    'Table'[Start_Dt] <= _MaxDate,
    OR ('Table'[End_Dt] >= _MinDate, ISBLANK('Table'[End_Dt]))
)

    I have attached my sample file for you to look at.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi erasm_w ,

    First, please make sure there is one date dimension table in your model just like the table "Calendar" in below screen shot. Then create a measure to get active clients, and create a line chart with field "Date" in date dimension table as Axis field and new measure as "Values" field.

    Active customer = 
VAR _curdate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Customer'[Cust_ID ] ),
        FILTER (
            'Customer',
            'Customer'[Start_Dt] < _curdate
                && OR ( ISBLANK ( 'Customer'[End_Dt] ), 'Customer'[End_Dt] > _curdate )
        )
    )

    Best Regards

    Rena