Cumulative totals getting split by year

[GunnerJ]

I'm trying to find the number of active customers by year. The values shown in the picture show how many customers are active that started service in that year. What I need is for the previous year values to add up to the current value. 

For example 2018 total is 2017+2018.              2019 total is 2017+2018+2019 and so on. 

This is the measure I'm using to get the value above. 

TEST = CALCULATE(COUNT('Attrition Rate'[ACCT]),FILTER(ALL('Attrition Rate'), 'Attrition Rate'[STARTDT]< MAX('Attrition Rate'[STARTDT]) && 'Attrition Rate'[MINDISCDT] = DATEVALUE("1/1/1800")&&'Attrition Rate'[STARTDT] <> BLANK()),USERELATIONSHIP('Attrition Rate'[STARTDT],'Attrition Date'[Date]))

The X-axis is from a date table which just shows Jan 1st 2017 to today. It has an inactive join to all of the dates shown in "Attrition Rate"

I beleive it'll come down to some sort of filter context but I could use a nudge in the right direction. Thank you and please let me know if you need any additional info.

[v-yalanwu-msft · Accepted solution]

Hi, @GunnerJ ;

You could modify it:

TEST2 = 
CALCULATE (
    COUNT ( 'Attrition Rate'[ACCT] ),
    FILTER (
        ALL ( 'Attrition Rate' ),
     YEAR('Attrition Rate'[STARTDT])<=YEAR( MAX('Attrition Date'[Date]))&&
            'Attrition Rate'[MINDISCDT] = DATEVALUE ( "1/1/1800" )
            && 'Attrition Rate'[STARTDT] <> BLANK ()))

And in last year ('2021') count is =8976

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.

[amitchandak]

@GunnerJ , Seem like very similar to this blog - Current employee https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Or

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[GunnerJ]

@amitchandak 
Here is the file. This is a test file so it's a bit unorganized. The visual in question is the "TEST by YearlyView" on the Filter Context Lost page. The measure is "TEST". I reviewed your post and beleive your active employees is in general what I'm trying to accomplish here. I'm not as familiar with some of the functions you used so I had a bit of difficulty following. I hope this file works and please let me know if you have any questions. Thank you for taking a look!

https://www.dropbox.com/s/8ssv0xw1p86hbin/Test%20Attrition%20File.pbix?dl=0

[GunnerJ]

@amitchandak any chance the file I provided was something you could work with?