Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
GunnerJ
Post Patron
Post Patron

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.

@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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.