Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
We have a table of customers that have contract start dates. We also have a table of website activities for those customers.
Account Table
Customer Name | Contract Start Date
Activity Table (Each line is an 1 activity)
Customer Name | Date of Activity | Days since Contract Start
We have a graph which shows the 'COUNTof Activities' on Y and 'Days since Contract Start' on X to see, after signing up, what activity looks like for customers. We want to normalize this for the number of accounts live. We tried to use a DISTINCTCOUNT function and divide 'COUNTof Activities' by 'DISTINCTCOUNT of customer names' but the problem is sometimes no actions occur even though they are live. We need to have a calculation which counts the number of accounts that have been live X number of days since contract start essentially so we can use this to divide activity count by.
Solved! Go to Solution.
Hi @Anonymous ,
You can try to use following measure formula to count 'activity date' based on current 'contract start date':
Measure = VAR currDate = MAX ( Table[Contract Start Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Table[DAte of Activity] ), FILTER ( ALLSELECTED ( Table ), [Date of Activity] = currDate ), VALUES ( Table[Customer Name] ) )
If above not help, please share some sample data and expected result to help us clarify your requirement.
Regards,
Xiaoxin Sheng
Hi @Anonymous ,
You can try to use following measure formula to count 'activity date' based on current 'contract start date':
Measure = VAR currDate = MAX ( Table[Contract Start Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Table[DAte of Activity] ), FILTER ( ALLSELECTED ( Table ), [Date of Activity] = currDate ), VALUES ( Table[Customer Name] ) )
If above not help, please share some sample data and expected result to help us clarify your requirement.
Regards,
Xiaoxin Sheng
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |