Active Company Count

[Gaurav_Lakhotia]

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,

Category	2016	2017	2018	2019	2020
Mobility	1	2	1	2	3
Insurance	1	2	4	2	2
Technology	1	2	3	2	2
Stationary	1	1	1	1	2

Here is the file with Sample Data .

Thanks in advance 🙂

[v-xulin-mstf · Accepted solution]

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.

[MattAllington]

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]

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

Didn't get the expected result with this.