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

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