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.
Hi,
I want a dynamic measure to be able to calculate the AVERAGE difference between two dates of a same column. The trick is that the calculation is between two status, which are "First contact" and "Contacted". The second trick is that there could be more that one subscribtion for the same ID (so you need to pick the last date per status).
Here's my draft :
CALCULATE(
AVERAGEX(
TABLE,
DATEDIFF(
CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="First contact") ,
CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="Contacted") ,
DAY))
FILTER(
CALCULATE(LASTDATE(TABLE[Date]) , TABLE[Status]="First contact") < CALCULATE(LASTDATE(TABLE[Date]) ,
TABLE[Status]="Contacted")
)
But, of course, the result is blank...
Here's my data sample.
Index | ID | Name | Status | Date |
1 | 2222200 | Name1 | First contact | 2020-10-01 |
2 | 2222201 | Name2 | First contact | 2020-10-02 |
3 | 2222202 | Name3 | First contact | 2020-10-03 |
4 | 2222200 | Name1 | Contacted | 2020-10-12 |
5 | 2222201 | Name2 | Contacted | 2020-10-12 |
6 | 2222202 | Name3 | Contacted | 2020-10-12 |
7 | 2222203 | Name4 | Contacted | 2020-10-13 |
8 | 2222200 | Name1 | Subscribed | 2020-11-24 |
9 | 2222200 | Name2 | Subscribed | 2020-11-25 |
10 | 2222200 | Name3 | Subscribed | 2020-11-26 |
Can you help me?
Thank you
Use variables to construct your logic step by step
- create a table variable that holds all unique IDs
- add columns that hold the latest "First Contact" date for each ID and the latest "Contacted" date for the same ID
- eliminate rows where either of these fields are missing
- calculate the averagex of the date diff for the remaining rows.
Let me know if you would like help with the DAX code.
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 | |
26 | |
19 | |
14 | |
10 |
User | Count |
---|---|
58 | |
50 | |
44 | |
19 | |
18 |