Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multi Date Comparison

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!

  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Anonymous,

     

    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

7 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

     

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thank you very much, Yuliana!

  • CahabaData's avatar
    CahabaData
    Memorable Member
    9 years ago

    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.

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      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's avatar
        CahabaData
        Memorable Member
        9 years ago

        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.