Homologating/Matching weeks between different terms

[Nuuk]

I am currently working a dashboard for an institution that has three different terms per year. They’d like to compare their actual progress to the past term (-1) and to the correspondent to the past year (-3), however when comparing to the past term, week numbers don’t match. For example:

And I’d like to achieve this (This I’ve achieved it by using a lookup table so that I can match the weeks from the past term to this one):

However, my question is if this is achievable from the original date table without the need to use an additional look up table, if this would be the best practice, and how could it be done?

I’d appreciate any help.

[v-rzhou-msft · Accepted solution]

Hi @Nuuk ,

I suggest you to create a DimWeekandTerm table to help your calculation.

DimWeekandTerm = 
GENERATE(VALUES('Table'[Week Number]),VALUES('Table'[Term]))

Term Sort = 
RANKX(DimWeekandTerm,'DimWeekandTerm'[Term],,ASC,Dense)

Measure:

Measure = 
VAR _CURRENTTERM =
    MAXX ( ALL ( 'DimWeekandTerm' ), 'DimWeekandTerm'[Term Sort] )
VAR _PART1 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week Number]
                = MAX ( DimWeekandTerm[Week Number] ) - 16
                && 'Table'[Term] = MAX ( DimWeekandTerm[Term] )
        )
    )
VAR _PART2 =
    CALCULATE (
        SUM ( 'Table'[Value] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Week Number] = MAX ( DimWeekandTerm[Week Number] )
                && 'Table'[Term] = MAX ( DimWeekandTerm[Term] )
        )
    )
VAR _TermStart =
    CALCULATE (
        MIN ( DimWeekandTerm[Week Number] ),
        FILTER (
            ALL ( DimWeekandTerm ),
            CALCULATE (
                SUM ( 'Table'[Value] ),
                FILTER (
                    ALL ( 'Table' ),
                    'Table'[Week Number] = EARLIER ( DimWeekandTerm[Week Number] )
                        && 'Table'[Term] = EARLIER ( DimWeekandTerm[Term] )
                )
            )
                <> BLANK ()
                && DimWeekandTerm[Term Sort] = _CURRENTTERM
        )
    )
RETURN
    IF (
        MAX ( DimWeekandTerm[Week Number] ) >= _TermStart,
        IF (
            MAX ( DimWeekandTerm[Term Sort] )
                IN {
                _CURRENTTERM - 1,
                _CURRENTTERM - 3
            },
            _PART1,
            _PART2
        )
    )

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.