Date Range in PowerPivot

[cmcreynolds]

Hello - I'm looking at making various calculations in my Power Pivot Data Model based on college student information (total number of courses, total tuition, etc.) Students may take more than one course in a [startdate] (aka term).

I pull my data using Power Query and I have a custom field [OneYearOut - calculated in PQ] that just adds a year to the [startdate] field. I also have built a relationship from my data to a calendar table.

My task is to look at all information within a year from a student's FIRST [startdate]. I know there are calculations using DATESBETWEEN and EARLIEST and LATEST, but all the examples I have seen on mrescel.com confuse me as to when to use the date in my data vs the related date in the calendar table.

[TABLE="width: 500"]
<tbody>[TR]
[TD]StudentID[/TD]
[TD]StartDate[/TD]
[TD]OneYearOut[/TD]
[TD]CourseCode[/TD]
[TD]CreditHours[/TD]
[TD]Total Tuition[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]3/15/2013[/TD]
[TD]3/15/2014[/TD]
[TD]ENG100[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]3/15/2103[/TD]
[TD]3/15/2014[/TD]
[TD]MATH101[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]6/30/2013[/TD]
[TD]6/30/2014[/TD]
[TD]SOC200[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]6/30/2013[/TD]
[TD]6/30/2014[/TD]
[TD]HIS300[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]A001[/TD]
[TD]4/15/2014[/TD]
[TD]4/15/2015[/TD]
[TD]MATH103[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]B001[/TD]
[TD]5/15/2014[/TD]
[TD]5/15/2015[/TD]
[TD]ENG100[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]B001[/TD]
[TD]8/31/2014[/TD]
[TD]8/31/2015[/TD]
[TD]PE202[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
[TR]
[TD]B001[/TD]
[TD]6/30/2015[/TD]
[TD]6/30/2016[/TD]
[TD]BUS105[/TD]
[TD]3[/TD]
[TD]$200[/TD]
[/TR]
</tbody>[/TABLE]

(PS - If I don't have to use my OneYearOut, I'm okay with leaving it out, I just thought it'd help)

So, in the above example, I want to summarize student A001 as having 12 credit hours and $800 in tuition; B001 as having 6 credit hours and $400 in tuition. (I hope that helps)

Thank you!

 

[cmcreynolds]

Oh, sorry -

Where - I was thinking a column entitled "One year total Credit" and "One year total tuititon" with them summed.

As for the other question, my supervisor wants a total of all the credit hours each student has taken in their first year, so , for student A - it would be rows 1-4, a total of 12 credits and $800. the fifth row wouldn't be included since that term started beyond a full year after student A's first term. For this report, I can just ignore anything that wasn't within the student's first year.

I hope I clarified further? Thank you again for your help, though

 

[ImkeF]

There we go then - I'd continue in Power Query

let
Source = Excel.CurrentWorkbook(){[Name="Tabelle1"]}[Content],
DataTable = Table.TransformColumnTypes(Source,{{"StudentID", type text}, {"StartDate", type date}, {"OneYearOut", type date}, {"CourseCode", type text}, {"CreditHours", Int64.Type}, {"Total Tuition", Int64.Type}}),
SortStartDate = Table.Sort(DataTable,{{"StartDate", Order.Ascending}}),
RemoveDupsStudentID = Table.Distinct(SortStartDate, {"StudentID"}),
DateRangeTable = Table.SelectColumns(RemoveDupsStudentID,{"OneYearOut", "StartDate", "StudentID"}),
MergeDataTable = Table.NestedJoin(DateRangeTable,{"StudentID"},DataTable,{"StudentID"},"NewColumn",JoinKind.LeftOuter),
ExpandData = Table.ExpandTableColumn(MergeDataTable, "NewColumn", {"StartDate", "OneYearOut", "CourseCode", "CreditHours", "Total Tuition"}, {"StartDate.1", "OneYearOut.1", "CourseCode", "CreditHours", "Total Tuition"}),
AddFilterColumn = Table.AddColumn(ExpandData, "Filter", each if [StartDate.1]<[OneYearOut] then 1 else ""),
ApplyFilter = Table.SelectRows(AddFilterColumn, each ([Filter] = 1)),
DateRange = Table.AddColumn(ApplyFilter, "Date Range First Year", each Text.Combine({Text.From([StartDate], "de-DE"), Text.From([OneYearOut], "de-DE")}, " - "), type text),
RemoveColumns = Table.RemoveColumns(DateRange,{"OneYearOut", "StartDate", "StartDate.1", "OneYearOut.1", "Filter"})
in
RemoveColumns

LinkToFile

 

[cmcreynolds]

Ahh - I should have guessed you would suggest folding The way I worked around it was using SPSS (just because I'm familiar with it and knew it's capabilities). BUT, I like using PowerQuery.

Question about folding, though - if PQ updates the original data pull, I assume it will update everything "down the funnel" too, right?

 

[cmcreynolds]

How do you join/merge the original table with the one you filtered? (I was following until the MergeDataTable line). Also, did you do this by code or by using the menus in PowerQuery Editor?

 

[ImkeF]

Yes, that's the trick where you have to start editing the code

I've started using the UI as a self join (join with the same query). Then the code looks like this:

MergeDataTable = Table.NestedJoin(DateRangeTable,{"StudentID"},DateRangeTable,{"StudentID"},"NewColumn",JoinKind.LeftOuter),

Then you manually edit it to this:

MergeDataTable = Table.NestedJoin(DateRangeTable,{"StudentID"},DataTable,{"StudentID"},"NewColumn",JoinKind.LeftOuter),

Replacing the reference to the same step by a reference to a pre-previous step.

This is one of the coolest PQ features in my eyes: Every step in PQ creates a temporary table (or other output forms like: List or record) that is normally forgotten (dropped) and replaced by the next step. But if you need them at a later step (or just a column from them or just a cell...) you simply reach out to them and they deliver again