Forum Discussion

s--turn's avatar
s--turn
Icon for Helper II rankHelper II
3 years ago
Solved

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

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 IDValid FromValid To
123-XYZ01/01/202131/12/2021
456-ABC04/10/202031/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-EndCount of Subscriptions
20201
20212

 

Any ideas?

 

Many thanks!

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.