YTD Calculations by User/Month

[DBB1]

I'm having problems trying to calculate YTD values for monthly employee data. My data is structured similar to this and spans several years.

Each Employee has individual values for any given month/year. The list of employees change as folks come and go, making it difficult to use Offset (this is a pretty large workbook with several pivots, slicers, data cube and VBA - it's already slow enough):

I'm trying to come up with a formula that would allow me to calculate YTD for the MonthTotal values for each employee. I've attached a screenshot of similarly structured data - (I apologize, but my organization will not allow me to install the XL2BB application).

Note that every employee has a monthly entry, resulting in several rows of duplicated "Period" values for each month-year. This has tripped me up when trying to use some of the other YTD calculations I've found, but I'm guessing I need to combine with some sort of Match on the Employee name. I've been pulling my hair out for weeks and have posted on other forums, but have had no takers.

Any ideas how to go about calculating the YTD for each employee? Any and all help is greatly appreciated:

 

[alansidman]

Cannot manipulate data in a picture. Please reload using XL2BB. Help us to help you by making it easy to process and test your data.

 

[DBB1]

As mentioned, my organization will not allow me to install the XL2BB macros. Is there an alternate way to upload my sample spreadsheet?

 

[alansidman]

Hope this is what you want

Book4
	A	B	C	D	E	F	G	H	I	J	K
1	Period	Employee	Districtl	District2	MonthTotal			Employee	YTD Dist1	YTD Dist2	YTD Total
2	Sep-22	Jessica	102	1023	1125			Jessica	2686	15873	18559
3	Sep-22	Mark	65	6458	6523			Mark	2256	34313	36569
4	Sep-22	Heather	652	2408	3060			Heather	3209	32509	35718
5	Sep-22	Jason	694	475	1169			Jason	694	475	1169
6	Oct-22	Jessica	566	684	1250			Terri	532	1238	1770
7	Oct-22	Mark	654	2581	3235			Terry	845	2560	3405
8	Oct-22	Heather	981	6425	7406
9	Nov-22	Jessica	735	1257	1992
10	Nov-22	Mark	483	4541	5024
11	Nov-22	Heather	575	7125	7700
12	Dec-22	Jessica	588	3212	3800
13	Dec-22	Mark	208	9614	9822
14	Dec-22	Terri	175	696	871
15	Dec-22	Heather	345	2573	2918
16	Jan-23	Terri	357	542	899
17	Jan-23	Jessica	24	4236	4260
18	Jan-23	Mark	325	5427	5752
19	Jan-23	Heather	417	8124	8541
20	Feb-23	Jessica	671	5461	6132
21	Feb-23	Terry	845	2560	3405
22	Feb-23	Mark	521	5692	6213
23	Feb-23	Heather	239	5854	6093
Sheet2

Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Period", type date}, {"Employee", type text}, {"Districtl", Int64.Type}, {"District2", Int64.Type}, {"MonthTotal", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Employee"}, {{"YTD Dist1", each List.Sum([Districtl]), type nullable number}, {"YTD Dist2", each List.Sum([District2]), type nullable number}, {"YTD Total", each List.Sum([MonthTotal]), type nullable number}})
in
    #"Grouped Rows"

 

[DBB1]

Sort of. I hadn't considererd putting it into a separate table. Any way to get it into a YTD column that presents the running YTD total for MonthTotal?

 

[alansidman]

Not in my skill bank. Maybe someone else will come along shortly. Be patient.

 

[DBB1]

Thank you so much. I'm sure I can use your code in another section. I've been pulling my hair out over this.

 

[alansidman]

Actually found a way with a calculated field in a Pivot Table

Row Labels	Sum of Districtl	Sum of District2	YTD
Heather
2022
Sep	652	2408	3060
Oct	981	6425	10466
Nov	575	7125	18166
Dec	345	2573	21084
2023
Jan	417	8124	8541
Feb	239	5854	14634
Jason
2022
Sep	694	475	1169
Jessica
2022
Sep	102	1023	1125
Oct	566	684	2375
Nov	735	1257	4367
Dec	588	3212	8167
2023
Jan	24	4236	4260
Feb	671	5461	10392
Mark
2022
Sep	65	6458	6523
Oct	654	2581	9758
Nov	483	4541	14782
Dec	208	9614	24604
2023
Jan	325	5427	5752
Feb	521	5692	11965
Terri
2022
Dec	175	696	871
2023
Jan	357	542	899
Terry
2023
Feb	845	2560	3405

Look at this link Add a Running Total in a Pivot Table

 

[DBB1]

The running total in the pivot assumes that there is only one entry for January, one for February, etc. whereas my data has several entries per month, for each employee. The YTD calculation needs to be by employee, down the column.

What you posted above came in like html table data - is that something I can use directly in Excel?

 

[alansidman]

If you build a pivot table, it will sum the monthly data by individual. As to second question, I don't understand what you are asking.