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!

 

[Ozeroth]

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]

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]

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]

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

 

[ImkeF]

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]

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

 

[ImkeF]

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]

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]

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?