Pivot data returning earliest date and latest date (only) counts by a training class number

[SerenityNetworks]

I need to generate a Pivot Table and Chart. I believe standard Pivot Table functionality is not up to the task, and I'll need Power Pivot. I'm okay with either, but I need guidance. I do need to avoid using summary columns in/beside the source data if at all possible.

I assist an organization that helps the needy. The organization conducts 9-week classes. A student's status is tracked over time for such things as do they have transportation, what is their housing status, do they have employment, do they have insurance, etc. I would like to generate a set of Pivot Tables and Charts that will show, by class number, client status counts upon entering the program (i.e. the earliest update date for the client) and their latest status (i.e. the latest update date for the client). Update information that falls between the earliest and latest dates will be ignored. For example, if there are status updated made 1/1, 2/1, 3/1, and 6/1, then the 2/1 and 3/1 updates are ignored. Note: client information is most commonly not updated on the same dates.

The source data would look something like this:

Class	Name	Updated	Housing	Transportation
45​	Aaron	1/1/2023​	Homeless	No
45​	Aaron	2/1/2023​	Apartment	No
45​	Aaron	6/1/2023​	Apartment	Yes
45​	Bob	1/1/2023​	Homeless	No
45​	Bob	2/1/2023​	Incarcerated	N/A
45​	Charlotte	1/1/2023​	Apartment	No
45​	Charlotte	1/2/2023​	Apartment	Yes
45​	Charlotte	3/1/2023​	Apartment	Yes
46​	Dawn	6/1/2023​	Homeless	No
46​	Dawn	7/1/2023​	Apartment	No
46​	Dawn	10/1/2023​	Apartment	Yes
46​	Eric	6/2/2023​	Apartment	Yes
46​	Eric	7/1/2023​	Apartment	No
46​	Eric	12/1/2023​	Apartment	Yes
47​	Frank	6/1/2023​	Homeless	No
47​	Frank	8/1/2023​	Homeless	Yes
47​	Gina	6/1/2023​	Apartment	No
47​	Gina	7/1/2023​	Apartment	No
47​	Gina	8/1/2023​	Apartment	Yes
47​	Gina	1/1/2024​	Home	Yes
47​	Henry	6/3/2023​	Apartment	No
47​	Henry	7/1/2023​	Homeless	Yes
47​	Henry	10/1/2023​	Apartment	Yes
47​	Henry	2/2/2024​	Home	Yes

The results table would be something like the following (first row added for clarity on what's being returned in the second row and below):

		Housing	Housing	Housing	Housing	Transportation	Transportation	Transportation
Class	Update	Homeless	Apartment	Incarcerated	Home	Yes	No	N/A
45	Earliest	2	1	0	0	0	3	0
45	Latest	0	2	1	0	2	0	1
46	Earliest	1	1	0	0	1	1	0
46	Latest	0	2	0	0	2	0	0
47	Earliest	1	2	0	0	0	3	0
47	Latest	1	0	2	0	3	0	0

Right now, I'm at a loss as how to limit the results table to only the data needed/shown. I've tried using standard pivot functionality using Class as a Row field, Update as a Row field filtered by value, Housing and Transportation as a Column field, and Transportation as a Value field set to Count. That's not getting me what I need - at all.

I need some guidance with as much detail as you have time to provide. I'm not well acquainted with Power Pivot.

Thanks,
Andrew

 

[SerenityNetworks]

I posted it there because it's easier to use an attachment for things like this.

Absolutely.
Plus, OzGrid was the first Excel forum I participated in. At one time, long ago, I was good at Excel and contributed often. These days, in retirement, I've fallen way behind on Excel's capabilities.

 

[Sam_D_Ben]

@SerenityNetworks, I hope Rory's solution helped you to understand it.

 

[SerenityNetworks]

All that we've discussed is working quite well. Thank you VERY much.

But the resulting pivot table revealed some data issues. I need to add the option of restricting the data by whether the student has ever graduated from a class or not. So, I added a column in the source data that has a formula which returns Yes or No on whether the student has ever graduated from a class. I modified the query to include the new column, but of course the pivot table just places the yes/no under the "Ever Graduated" heading. I need to move it so that I can constrain the pivot results as I do for "Class Number". But I'm at a loss as how to do this.

 

[SerenityNetworks]

@RoryA

Update to the above post. Here is the current query.

let
Source = Excel.CurrentWorkbook(){[Name="InfoAndHistory"]}[Content],
#"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Lastname, Firstname", type text}, {"Update Date", type datetime}, {"Updated by", type text}, {"Class(es) Attended", type text}, {"First Class", type number}, {"Ever Graduated", type text}, {"Contact Method", type text}, {"Current Phone", type any}, {"Ext / Alt / Ph Notes", type text}, {"Address", type text}, {"Email", type text}, {"Employed?", type text}, {"Employer?", type text}, {"How long at current employer (in months)?", Int64.Type}, {"Wage Increase?", type text}, {"Notes, Recognition, Awards, etc.", type text}, {"Deceased", type text}, {"Residence", type text}, {"Food and Nutrition", type text}, {"Childcare", type text}, {"Reliable Transportation", type text}, {"Insurance", type text}, {"Student Video", type any}}),
#"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Updated by", "Class(es) Attended", "Contact Method", "Current Phone", "Ext / Alt / Ph Notes", "Address", "Email", "Employer?", "How long at current employer (in months)?", "Wage Increase?", "Notes, Recognition, Awards, etc.", "Deceased", "Student Video"}),
#"Reordered Columns" = Table.ReorderColumns(#"Removed Columns",{"First Class", "Lastname, Firstname", "Update Date", "Ever Graduated", "Employed?", "Residence", "Food and Nutrition", "Childcare", "Reliable Transportation", "Insurance"}),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"First Class", "Lastname, Firstname", "Update Date"}, "Attribute", "Value")
in
#"Unpivoted Other Columns"

 

[SerenityNetworks]

@RoryA

Doh! Never-mind. I was having a brain cramp. Once I thought about it, making the change was bonehead easy. However, I did find that I needed to add two new parameters, Ever Graduated and Updated. Ever Graduated lets me filter for if the student ever graduated in any class. Updated lets me filter out records where I don't have both a beginning record and a current record.

Thanks again for all your assistance.
Andrew