Forum Discussion
Drill through using inactive relationship
Hi Nabil20_24
I'm not entirely sure what you're trying to achieve since you haven't provided the expected result, just descriptions based on the sample data. However, for active clients, shouldn't the date range be from the start of their activity until just before inactivity, rather than only the active date? As for inactive clients, do you want to count them only in the month they became inactive or continue counting them from that point onward?
You might find the attached pbix useful.
You're right! I should have included the expected output. To count the number of active clients, we need to count the number of client names where the status is 'Active'. To count inactive clients, we need to count the number of client names where the inactive date is not blank.
For example, if I have the dataset below and select the year 2022 from the slicer, the bar chart should show 2 active clients in January 2022 and 0 inactive clients. If I select 2023 from the slicer, In Jan it should show 3 inactive clients and 1 active client
| Client Name | Status | Active Date | Inactive Date |
| Oliver | Inactive | 01/01/2022 | 08/01/2023 |
| Ethan | Inactive | 02/01/2022 | 23/1/2023 |
| Michael | Inactive | 03/01/2022 | 15/1/2023 |
| George | Inactive | 04/01/2022 | 05/09/2024 |
| Chris | Active | 01/01/2022 | |
| Natalie | Active | 02/01/2022 | |
| Ian | Active | 03/01/2023 |