How to calculate cumulative active years?

[bourne2000]

Hi

I would like to calculate cumulative active years for the members. Below is my data

For example member no: 9759 joined date is 19.08.2010 and inactive year (discontinued year) is 10.01.2020 . This memeber 9759 was active from 2010 to 2020. From 2021 he is not acrtive. I need to calculate this for all the members. 

Then I need to calculate the cumulative count active years, 

Example, if joining year 2010, 10 people joins then it's 10. If Year 2011, 10 people joins but 5 people leaves (calculate from inactive years) then the total active that year is accumulated with the ones still active from year 2010. So total will be 10+10-5 = 15. I need to calculate this cumulative count.

Can anyone advise how to calculate this measure?

https://we.tl/t-ILf0ioMOSq

[v-yalanwu-msft · Accepted solution]

Hi, @bourne2000 ;

You could try it.

Measure = 
CALCULATE (
    COUNT ( [Membership Number] ),
    FILTER (
        ALL ( 'Attachment_1621716164 (3)' ),
        [Join Date].[Year] <= MAX ( 'Attachment_1621716164 (3)'[Joining Year] )
            && [Inactive Date].[Year] >= MAX ( 'Attachment_1621716164 (3)'[Joining Year] ) ))

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AlexisOlson]

You should be able to do a sum like this assuming there's only one row per member in the table:

SUMX (
    Table1,
    DATEDIFF (
        Table1[Join Date],
        COALESCE ( Table1[Inactive Date], TODAY () ),
        YEAR
    )
)

For any member who has an inactive date, it uses that. Otherwise, it uses today's date. (See COALESCE).

[bourne2000]

@AlexisOlson Thanks for your support

I tried the measure and it's working. However, results are not correct. For example, data starts from 1924. In the year 1924, total 107 members have joined. However, the measure shows 1838 membere were joined

Can you please check and help?

[AlexisOlson]

Sorry, I misunderstood and was adding up years not the number of people. I think 1838 is the total number of member-years through today for members who joined in 1924. You're asking for something rather different than I first thought.

If I'm now understanding correctly, I think you want something more like this:

VAR EndOfYear = DATE ( MAX ( Table2[Joined Year] ), 12, 31 )
RETURN
    COUNTROWS (
        FILTER (
            ALL ( Table1 ),
            Table1[Join Date] <= EndOfYear
                && ( Table1[Inactive Date] > EndOfYear
                        || ISBLANK ( Table2[Inactive Date] ) )
        )
    )

[bourne2000]

@AlexisOlson  Thank you very much. This measure is not working