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!

@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
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors