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....
  • 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

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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

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

       

       

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

        Hi Anonymous,

         

        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