Calculate Running Total vs Year End Total- two different columns

[anthlen]

Hi All,

Forgive me if this has been discussed before-

I am looking to calculate a running total of one column(Attrition) vs Year-end Total(Roster) in another column - This is for calculating employee Attrition

I have one DataSet- we count the employee twice if they left

Roster	Attrition	Employee	Effective Year	Effective Month
1		A	2017	January
1		B	2017	March
1		C	2017	April
	1	A	2017	March
	1	B	2017	April

I would like to calculate the following- I envision it like this so I can create a cumulative line bar chart.  I would like to use the last line.  I would also have multiple years in this.

Month	Attrtion Total	Attrition Running Total	2017 Roster Year Total	****bleep**** % Attrition
Jan	0	0	3	0%
Feb	0	0	3	0%
Mar	1	1	3	33%
Apr	1	2	3	67%

Any thoughts?

[v-jiascu-msft · Accepted solution]

Hi @anthlen

I would suggest that evaluating one year a time with a slicer based on year. Roster_Year_Total would be easy.

Roster_Year_Total =
CALCULATE (
 SUM ( 'table'[Roster] ),
 ALLEXCEPT ( 'Table', 'Table'[Effective Year] )
)

Then we could get the running total of Attrition.

Running_Attrition =
CALCULATE (
    SUM ( 'table'[Attrition] ),
    FILTER (
        ALL ( 'table' ),
        'table'[effective month] <= MAX ( 'table'[effective month] )
    )
)

Finally we get the percentage.

% = [Running_Attrition]/[Roster_Year_Total]

Best Regards!

Dale