Forum Discussion

Gaurav_Lakhotia's avatar
Gaurav_Lakhotia
Helper III
5 years ago
Solved

Active Company Count

Hello Everyone!!

Need your help in solving my problem, I'm trying to calculate/create a table which will give me distinct count of companies which are associated with me for particular year.

For example,

 

In above snapshot, we have Join Year and End Year. We're marking "9999" for the companies who are still associated with us and we've proper year if they are terminated. Company with End Year "9999" should be counted in next years.

 

Result would be something like this,

Category20162017201820192020
Mobility12123
Insurance12422
Technology12322
Stationary11112

 

Here is the file with Sample Data .

 

Thanks in advance 🙂

Need Help
Tips and Tricks
  • v-xulin-mstf's avatar
    v-xulin-mstf
    5 years ago

    Hi Gaurav_Lakhotia,

     

    Create a dimensional table as:

    Table =

VALUES('Company Tbl'[Join Year])

    Try measure as:

    Measure =

CALCULATE(

    COUNT('Company Tbl'[Category]),

    FILTER(

        ALL('Company Tbl'),

         'Company Tbl'[Join Year]<=MAX('Table'[Year]) && 'Company Tbl'[End Year]>=MAX('Table'[Year]) && 'Company Tbl'[Category]=MAX('Company Tbl'[Category])

))

    Here is the output:

     

    Here is the demo, please try it: Active Company Count

    Best Regards,

    Link

     

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

3 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    5 years ago

    You need a calendar table (just years in your case).  Do not join it to the data table. Then write a measure, something like this


    Result = sumx(dataTable,

    If(Calendar[Year] >= dataTable[Join Year] && Calendar[Year] <=dataTable[end year],1))

     

    put calendar year on columns in the matrix. 

    • Gaurav_Lakhotia's avatar
      Gaurav_Lakhotia
      Helper III
      5 years ago

      Hey MattAllington , Sorry for late response, I thought I've turned On the notification. 

      Didn't get the expected result with this.

  • v-xulin-mstf's avatar
    v-xulin-mstf
    Community Support
    5 years ago

    Hi Gaurav_Lakhotia,

     

    Create a dimensional table as:

    Table =

VALUES('Company Tbl'[Join Year])

    Try measure as:

    Measure =

CALCULATE(

    COUNT('Company Tbl'[Category]),

    FILTER(

        ALL('Company Tbl'),

         'Company Tbl'[Join Year]<=MAX('Table'[Year]) && 'Company Tbl'[End Year]>=MAX('Table'[Year]) && 'Company Tbl'[Category]=MAX('Company Tbl'[Category])

))

    Here is the output:

     

    Here is the demo, please try it: Active Company Count

    Best Regards,

    Link

     

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