# Date Range in PowerPivot



## cmcreynolds (Sep 17, 2015)

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.  


StudentIDStartDateOneYearOutCourseCodeCreditHoursTotal TuitionA0013/15/20133/15/2014ENG1003$200A0013/15/21033/15/2014MATH1013$200A0016/30/20136/30/2014SOC2003$200A0016/30/20136/30/2014HIS3003$200A0014/15/20144/15/2015MATH1033$200B0015/15/20145/15/2015ENG1003$200B0018/31/20148/31/2015PE2023$200B0016/30/20156/30/2016BUS1053$200

<tbody>

</tbody>
(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!


----------



## Ozeroth (Sep 18, 2015)

Here is one way of doing it for CreditHours. I've called your tables Data & Calendar. The OneYearOut column isn't needed for this measure.

The innermost CALCULATETABLE finds the earliest date for selected students, ignoring all filters except Student.
The outer CALCULATETABLE creates a table of dates between this earliest date and one year later.
CALCULATE calculates the sum of CreditHours over this one year date range.

You may want to change the behaviour when multiple students are selected. At the moment, this measure finds the earliest StartDate among all students and sums everyone over the same date range.


```
[CreditHours for Selected Students in First Year] :=
CALCULATE (
    SUM ( Data[CreditHours] ),
    CALCULATETABLE (
        DATESBETWEEN (
            Calendar[Date],
            VALUES ( Calendar[Date] ),
            DATEADD ( VALUES ( Calendar[Date] ), 1, YEAR )
        ),
        CALCULATETABLE (
            FIRSTDATE ( SUMMARIZE ( Data, Calendar[Date] ) ),
            ALLEXCEPT ( Data, Data[StudentID] ),
            ALL ( Calendar )
        )
    )
)
```


----------



## cmcreynolds (Sep 21, 2015)

Yes, I think that is my issue is that the date range is going to be different for each student, potentially (in other words, there will be SOME with the same date range, but not all of them)


----------



## ImkeF (Sep 22, 2015)

Not sure what you're trying to achieve here: Daily allocation of your fees and hours over the course term? 

This doesn't seem to match the number examples you've provided: 


Student A with 800 USD would pay this amount in 2013 if he would pay once he started the course. But he would also have to pay 200 in 2014.
Student B would have to pay nothing in 2013, as his courses start in 2014: 400 then and 200 in 2015 (so one year later).

Or do you want to allocate the fees to the relative start years of your students (600 in first year, 200 in second aso)?

? How about a picture of how your desired report should exactly look like ?


----------



## cmcreynolds (Sep 22, 2015)

Imke - sort of - but $800 is how much student A had between 3/15/2013 and 3/15/2014.


----------



## ImkeF (Sep 22, 2015)

So which field do you want to drag into the rows section of your Pivot Report?: 

A field from your data table from above or from your calendar table?


----------



## cmcreynolds (Sep 24, 2015)

I'd like my rows to be the student IDs.


----------



## ImkeF (Sep 24, 2015)

OK. 
So where shall the date reference period "between 3/15/2013 and 3/15/2014" then be shown? 
In the column headers?
Or do you simply want to put a filter on it instead of showing it in your report?


----------



## cmcreynolds (Sep 24, 2015)

Well, it would change depending on each student. It would be "between 3/15/2013 and 3/15/2014" for student A001 and "between 5/15/2014 and 5/15/2015" for student B001.


----------



## ImkeF (Sep 24, 2015)

This is *what *shall be shown. 

Question is: *Where *shall this be shown: Row section or column? Just in a filter area/slicer?

& How about the other date ranges: For Student A: 30/06/2013 til 30/06/2014 and 4/15/2014 til 4/15/2015: Do you want to filter them out manually or is there another time element that shall be taken into account as well (like calendar year where the students range starts)?

Or shall it always only take the first daterange per student - but what further then?: How do we end up with 800 USD for Student A, and not 1000 as this is his total cumulated fee?


----------



## cmcreynolds (Sep 17, 2015)

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.  


StudentIDStartDateOneYearOutCourseCodeCreditHoursTotal TuitionA0013/15/20133/15/2014ENG1003$200A0013/15/21033/15/2014MATH1013$200A0016/30/20136/30/2014SOC2003$200A0016/30/20136/30/2014HIS3003$200A0014/15/20144/15/2015MATH1033$200B0015/15/20145/15/2015ENG1003$200B0018/31/20148/31/2015PE2023$200B0016/30/20156/30/2016BUS1053$200

<tbody>

</tbody>
(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 (Sep 25, 2015)

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 (Sep 25, 2015)

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 (Sep 25, 2015)

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 (Sep 30, 2015)

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 (Sep 30, 2015)

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


----------

