Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Good Afternoon,
I was trying to find a way to pull numbers using a date range. We are wanting to check when a service was initially installed and have that show as an initial install count.
Address ID | Address | Install Date | Disconnect Date |
1 | 123 Sample Dr | 4/20/2018 | 9/18/2018 |
1 | 123 Sample Dr | 4/20/2018 | 9/18/2018 |
1 | 123 Sample Dr | 4/25/2018 | 2/23/2019 |
1 | 123 Sample Dr | 5/1/2019 |
In the example above I have a single address that we are wanting to show as an inital install count of 2 (1 on 4/20/2018 and 1 on 5/1/2019).
The service in the example was initially installed on 4/20/2018 (two services but we only want to count as a single initial install). They added another service five days later. So we would want to show on our report a initial install date of 4/20/2018 and ignore the 4/25/2018 since it was installed while the inital services were still active.
My issue comes up whenever a customer disconnects all services. In the example above the customer had at least one service active from 4/20/2018 to 2/23/2019. Then they had a break in service until 5/1/2019.
We would want the 5/1/2019 to show as an inital count of 1 for that date.
Can someone help me figure out how I would need to accomplish this or if it is even possible. On things like these we usually only care about the very first time a person got service and I would go to power query short the table based on install date then buffer the table then remove duplicates. However in this scenario we would want to know of any breaks in service and count anything after that break as a new count.
Solved! Go to Solution.
Hi @mattyj2009
You may add index first and then create columns and measures as below.Attached sample file for your reference.
Last_DisconnectDate = VAR last_Dis_date = CALCULATE ( MAX ( Data[Disconnect Date] ), FILTER ( ALLEXCEPT ( Data, Data[Address ID] ), Data[Index] = EARLIER ( Data[Index] ) - 1 ) ) RETURN IF ( last_Dis_date = BLANK (), Data[Disconnect Date], last_Dis_date )
New_index = IF(Data[Install Date]>Data[Last_DisconnectDate],Data[Index],0)
Count = VAR min_date = CALCULATE ( MIN ( Data[Install Date] ), FILTER ( ALL ( Data ), Data[Address ID] = MAX ( Data[Address ID] ) && Data[New_index] = MAX ( Data[New_index] ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Data[Address ID] ), FILTER ( Data, IF ( Data[Install Date] = min_date, 1 ) ) )
CountSum = SUMX(VALUES(Data[Install Date]),[Count])
Regards,
Hi @mattyj2009
You may add index first and then create columns and measures as below.Attached sample file for your reference.
Last_DisconnectDate = VAR last_Dis_date = CALCULATE ( MAX ( Data[Disconnect Date] ), FILTER ( ALLEXCEPT ( Data, Data[Address ID] ), Data[Index] = EARLIER ( Data[Index] ) - 1 ) ) RETURN IF ( last_Dis_date = BLANK (), Data[Disconnect Date], last_Dis_date )
New_index = IF(Data[Install Date]>Data[Last_DisconnectDate],Data[Index],0)
Count = VAR min_date = CALCULATE ( MIN ( Data[Install Date] ), FILTER ( ALL ( Data ), Data[Address ID] = MAX ( Data[Address ID] ) && Data[New_index] = MAX ( Data[New_index] ) ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Data[Address ID] ), FILTER ( Data, IF ( Data[Install Date] = min_date, 1 ) ) )
CountSum = SUMX(VALUES(Data[Install Date]),[Count])
Regards,
This is exactly what I needed. I really appreicate your help. Have a great day!!!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
117 | |
101 | |
71 | |
61 |