Forum Discussion

LeahS's avatar
LeahS
Frequent Visitor
3 years ago
Solved

Active Clients in time frame

I have the below data.  I'm trying to create a measure that will be tied to my date slicer (which filters by month & year).  I want the measure to count how many active clients there were in the month selected based on client start date and end date.  I tried to use a var_date syntax I saw on-line, but since I'm new to power BI, I couldn't make it work with 2 conditions.  Does anyone have any ideas on what might work?  It would also be gre

ClientStart_DateDischarge_date
Le St10/17/2022 
Pe Se7/14/2022 
Ch Pa5/1/20236/1/2023
Zo Gi2/13/20237/2/2023
Be Sp12/21/20223/15/2023
Fa Gi11/20/2022 

at if I could calculate current active clients, but I couldn't get ISBLANK() to work for me.

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi LeahS ,

     

    I suggest you to create a measure as below to count the active clients.

    Count Active Clients =
VAR _SELECTSTART =
    MIN ( 'Date'[Date] )
VAR _SELECTEND =
    MAX ( 'Date'[Date] )
RETURN
    COUNTX (
        FILTER (
            'Lincoln Client Info',
            'Lincoln Client Info'[Start Date] <= _SELECTEND
                && OR (
                    'Lincoln Client Info'[Discharge Date] >= _SELECTSTART,
                    'Lincoln Client Info'[Discharge Date] = BLANK ()
                )
        ),
        [Client]
    )

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

    • LeahS's avatar
      LeahS
      Frequent Visitor
      3 years ago

      Greg_Deckler 

      The first one works mostly, but I can't get the count right.  I think my column names are off.  Below is the measure I created.  Can you tell me where I went wrong?

      Active Clients L = VAR tmpclients = ADDCOLUMNS('Lincoln Client Info',"Active",IF(ISBLANK([Discharge Date]),TODAY(),[Discharge Date]))
      VAR tmpTable =  
      SELECTCOLUMNS(
          FILTER(
              GENERATE(
                  tmpclients,
                  'Date Table'
              ),
              [Date] >= [Start Date] &&
              [Date] <= [Discharge Date]
          ),
          "Active",[Start Date],
          "Date",[Date]
      )
      VAR tmpTable1 = GROUPBY(tmpTable,[Active],"Count",COUNTX(CURRENTGROUP(),[Date]))
      RETURN COUNTROWS(tmpTable1)
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi LeahS ,

         

        I suggest you to create a measure as below to count the active clients.

        Count Active Clients =
VAR _SELECTSTART =
    MIN ( 'Date'[Date] )
VAR _SELECTEND =
    MAX ( 'Date'[Date] )
RETURN
    COUNTX (
        FILTER (
            'Lincoln Client Info',
            'Lincoln Client Info'[Start Date] <= _SELECTEND
                && OR (
                    'Lincoln Client Info'[Discharge Date] >= _SELECTSTART,
                    'Lincoln Client Info'[Discharge Date] = BLANK ()
                )
        ),
        [Client]
    )

        Result is as below.

         

        Best Regards,
        Rico Zhou

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • LeahS's avatar
    LeahS
    Frequent Visitor
    3 years ago

    Thanks!  That worked.  And the syntax is easy enough to understand that I can adapt it for other measures.