Multi Date Comparison

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