How to calculate penetration of the company

[Layziane]

Hello, 

I need help for make a DAX about penetration.
Our database have several companies and the date are different that these companies start the operations.
For example, the "Company A" starts in october and the "Company B" starts in december. So, I need to calculate the penetranion from when the company enters.
Another example, in april have 10 companies with the total of 100 customers each (total of 1000 customers) and we made 250 contracts, the penetration is 25%.
In may enters a new company and we have 11 companies with 100 customers each (total of 1000 customers) and we made 250 contracts, the penetration is 22%. 
But if we want to calculate the penetration of april in november, we can't considerate the companies that starts after april.
In resume, we need just calculate the penetration when the company starts starts his operation. 

[Anonymous]

Hi @Layziane ,

Due to I don't know your data model, here I create a sample to have a test.

Company:

Contracts:

DimDate:

DimDate = ADDCOLUMNS(CALENDARAUTO(),"Year",YEAR([Date]),"Month",MONTH([Date]),"YearMonth",YEAR([Date])*100+MONTH([Date]))

Relationship:

Measure:

Measure = 
VAR _COMPANY =
    CALCULATE (
        COUNT ( Company[Company] ),
        FILTER ( ALL ( Company ), Company[Start Date] <= MIN ( DimDate[Date] ) )
    )
VAR _CUSTOMER = 100
VAR _CONTRACTS =
    CALCULATE (
        SUM ( Contracts[Contracts] ),
        FILTER (
            Contracts,
            Contracts[YearMonth] = SELECTEDVALUE ( DimDate[YearMonth] )
        )
    )
RETURN
    DIVIDE ( _CONTRACTS, _COMPANY * _CUSTOMER )

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.

[Anonymous]

Hi @Layziane 

Can you please provide sample Data.

[Layziane]

The "dataSolicitacaoEmprestino" is the date with the company starts, just start with the firts contract