Distinct Count of Id's between first and last date dynamically with calendar table

[Anonymous]

I have a table where I keep PersonId's from our application and then the first and last date of their subscriptions. These are normally in whole year spans. So the FirstSubStartDate would be 8/31/2021 and LastSubEndDate is 8/30/2022.

I also have a Reporting Calendar table that updates daily and allows me to chop up the data in any form I choose.

My struggle is finding a way to write a measure where I can choose what way to do a distinct count of these personid's and choose to graph it by year, month or whatever option I have in this reporting calendar.

Person Subscription Table

PersonId	FirstSubStartDate	LastSubEndDate
1186	8/31/2020	8/30/2022
34859	2/10/2020	2/9/2021
34889	8/8/2020	8/7/2021

Here is what Im looking for:
I need a measure so when I put it into a bar or line graph and have Months for the X Axis, that it provides the distinct number of Id's in that month.

So in the above examples, if I was looking at Jan-Dec in 2021, 1186 would count once in each month where 34859 would count in Jan and Feb and then 34889 would count Jan-Aug. Only 1186 would count beyond 8/2021 because its LastSubEndDate goes into 2022.

[jgeddes · Accepted solution]

Along with your example data I created a date table that was unrelated to the example data.
The measure I used was

Count of IDs =

CALCULATE(

    DISTINCTCOUNT('Table'[PersonId]),

    FILTER('Table', 'Table'[FirstSubStartDate] <= MAX(dimDate[Date]) && 'Table'[LastSubEndDate] >= MIN(dimDate[Date]) )

)

Using the date table date hierarchy as the x-axis I was able to get

You can then use the date hierarchy to view the data at the resolution you desire

[Anonymous]

I was really overthinking that. I had everthing except for the MIN/MAX. Thanks!