cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GunnerJ
Helper V
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. 
 
Cumulative total year split.PNG
 
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"
datetable relations.PNG
 
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
v-yalanwu-msft
Community Support
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

vyalanwumsft_1-1636685430109.png

The final output is shown below:

vyalanwumsft_0-1636685358819.png

vyalanwumsft_2-1636685460747.png


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.

View solution in original post

4 REPLIES 4
v-yalanwu-msft
Community Support
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

vyalanwumsft_1-1636685430109.png

The final output is shown below:

vyalanwumsft_0-1636685358819.png

vyalanwumsft_2-1636685460747.png


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
Super User
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.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
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 !!

@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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

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

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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