Forum Discussion

GunnerJ's avatar
GunnerJ
Post Patron
4 years ago
Solved

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.
Need Help
Tutorial Requests
  • v-yalanwu-msft's avatar
    v-yalanwu-msft
    4 years ago

    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