Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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.
The "dataSolicitacaoEmprestino" is the date with the company starts, just start with the firts contract
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
24 | |
20 | |
15 | |
12 |
User | Count |
---|---|
55 | |
48 | |
43 | |
19 | |
19 |