Identifying which year-ends (Dec 31st) are between two dates

[s--turn]

Hello,

I need to be able to produce counts for the total number of subscriptions that were active as of December 31st each year.  This is the kind of raw data I have:

Subscription ID	Valid From	Valid To
123-XYZ	01/01/2021	31/12/2021
456-ABC	04/10/2020	31/12/2021

If they were all complete years like the top row, I would just use the Valid To date.  However, records like the bottom row needs to be identified as active on both 31st December 2020 and 2021. 

I ultimately want to be able to get something like this:

Year-End	Count of Subscriptions
2020	1
2021	2

Any ideas?

Many thanks!

[s--turn]

Thank you, @v-rzhou-msft ! Much appreciated.

[v-rzhou-msft · Accepted solution]

Hi @s--turn ,

You can try this measure.

Count of Subscriptions =
CALCULATE (
    COUNT ( 'Table'[Subscription ID] ),
    FILTER (
        'Table',
        'Table'[Valid From] <= MAX ( 'Calendar'[Date] )
            && 'Table'[Valid To] >= MAX ( 'Calendar'[Date] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.