Multi Date Comparison

[BlueTeam]

I am looking at college registration data that contains the date in which a  student adds a class.  I would like to determine in which week of the term the class was added.  I have two tables:

1. Registration table which contains the Add_Date.

2. Term_Date table which contains: Term_Start, End_First_Week, End_Second_Week, End_Third_Week, Term_End (out to 11th week).

The Term_Date table is formatted with acolumn for each of the 11 weeks.  I could easly pivot this to rows if the calucaltion would be eaiser.

Question:  How would I compare the Add_Date to all the other dates to calculate which week the student added the course?  A huge IF statment?  I know there has to be a higher level function to help me with this but I've not been able to figure it out just yet.

Thanks in advance!

[CahabaData]

another entirely different approach would be to create a newTermDateTable where there are 2 columns: Date, Term

in your example there are 11 weeks and so this newTermDateTable would have 77 rows; 1 per actual date, and then place whichever term descriptor is appropriariate in the Term field.  That that term repeats for 11 rows then changes, etc.

with the newTermDateTable then join to your registration table by the Date fields.

[BlueTeam]

Not quite following you, CahabaData.  So if the table you are referencing looks like below, the actual registration date the student record has may fall between two of these rows, not necessarily equal to a specific date.  Only approach I can think of would be to write logic that identifies the two dates that the registration date falls between in order to identify the description row.  Correct?

Term     Date          WeekEndDate

1          1/4/2016      1

1         1/8/2016       2

1        1/15/2016      3

1        1/22/2016      4

1        1/29/2016      5

1        2/5/2016        6

....

Thanks!

[CahabaData]

in your post there are 11 weeks and so this newTermDateTable would have 77 rows; 1 per sequential date for 11 weeks....in your example there are not sequential dates, they have gaps....  with sequential there is every date - therefore you can simply do a join .

every Reg Table Add Date will have a corresponding date join in the newTermDateTable - and thereby you can use the info in the next field/column

I probably should refer to it as a Calendar Table as that is the term most frequently used in BI.

[BlueTeam]

Too obvious and I should have interpreted your earlier message that way.  Thank you again!

[v-yulgu-msft]

Hi @BlueTeam,

Have you resolved your issue? If yes, please kindly mark the corresponding reply as an answer so that others having similar requirement can find the solution more easily. If you still have any question, please feel free to ask. Thanks for your understanding.

Regards,
Yuliana Gu

[v-yulgu-msft · Accepted solution]

Hi @BlueTeam,

Unpivot table first to get below data structure.

Add two calculated columns using below DAX formula:

Rank =
RANKX (
    FILTER (
        Term_DateTerm,
        Term_DateTerm[Term Start] = EARLIER ( Term_DateTerm[Term Start] )
    ),
    Term_DateTerm[Week Date],
    ,
    ASC,
    DENSE
)

Previous Week =
IF (
    LOOKUPVALUE (
        Term_DateTerm[Week Date],
        Term_DateTerm[Term Start], Term_DateTerm[Term Start],
        Term_DateTerm[Rank], Term_DateTerm[Rank] - 1
    )
        = BLANK (),
    Term_DateTerm[Term Start],
    LOOKUPVALUE (
        Term_DateTerm[Week Date],
        Term_DateTerm[Term Start], Term_DateTerm[Term Start],
        Term_DateTerm[Rank], Term_DateTerm[Rank] - 1
    )
)

Cross join Registration table and Term_Date table.

Table 2 =
CROSSJOIN (
    'Registration table',
    SELECTCOLUMNS (
        Term_DateTerm,
        "Term Start", Term_DateTerm[Term Start],
        "Week", Term_DateTerm[Attribute],
        "Week Date", Term_DateTerm[Week Date],
        "Previous week date", Term_DateTerm[Previous Week]
    )
)

Filter above table returned via crossjoin.

Table 3 =
FILTER (
    'Table 2',
    'Table 2'[Add Date] >= 'Table 2'[Previous week date]
        && 'Table 2'[Add Date] <= 'Table 2'[Week Date]
)

Best regards,
Yuliana Gu

[BlueTeam]

Thank you very much, Yuliana!