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

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