cancel
Showing results for
Did you mean:
Helper V

## Cumulative totals getting split by year

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.
1 ACCEPTED SOLUTION
Community Support

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.

4 REPLIES 4
Community Support

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.

Super User

@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.

Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!
!! Subscribe to my youtube Channel !!
Helper V

@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

Helper V

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors