Last 4 Weeks Total (minus previous week)

[Anonymous]

I need to achieve the total sales in the last 5 weeks minus the previous week. 

A measure for the total sales and a column for YearWeek in the "YYYYW00" format has been created. 

What I am trying to achieve is:

2022W01 =2021W51 Sales + 2021W52 Sales + 2022W01 Sales + 2022W02 Sales
2022W02 =2021W52 Sales + 2022W01 Sales + 2022W02 Sales + 2022W03 Sales
2022W03 =2022W01 Sales + 2022W02 Sales + 2022W03 Sales + 2022W04 Sales
2022W04 =2022W02 Sales + 2022W03 Sales + 2022W04 Sales + 2022W05 Sales
2022W05 =2022W03 Sales + 2022W04 Sales + 2022W05 Sales + 2022W06 Sales
2022W06 =2022W04 Sales + 2022W05 Sales + 2022W06 Sales + 2022W07 Sales
2022W07 =2022W05 Sales + 2022W06 Sales + 2022W07 Sales + 2022W08 Sales
2022W08 =2022W06 Sales + 2022W07 Sales + 2022W08 Sales + 2022W09 Sales
2022W09 =2022W07 Sales + 2022W08 Sales + 2022W09 Sales + 2022W10 Sales
2022W10 =2022W08 Sales + 2022W09 Sales + 2022W10 Sales + 2022W11 Sales
2022W11 =2022W09 Sales + 2022W10 Sales + 2022W11 Sales + 2022W12 Sales
2022W12 =2022W10 Sales + 2022W11 Sales + 2022W12 Sales + 2022W13 Sales
2022W13 =2022W11 Sales + 2022W12 Sales + 2022W13 Sales + 2022W14 Sales
2022W14 =2022W12 Sales + 2022W13 Sales + 2022W14 Sales + 2022W15 Sales
2022W15 =2022W13 Sales + 2022W14 Sales + 2022W15 Sales + 2022W16 Sales
2022W16 =2022W14 Sales + 2022W15 Sales + 2022W16 Sales + 2022W17 Sales
2022W17 =2022W15 Sales + 2022W16 Sales + 2022W17 Sales + 2022W18 Sales
2022W18 =2022W16 Sales + 2022W17 Sales + 2022W18 Sales + 2022W19 Sales
2022W19 =2022W17 Sales + 2022W18 Sales + 2022W19 Sales + 2022W20 Sales
2022W20 =2022W18 Sales + 2022W19 Sales + 2022W20 Sales + 2022W21 Sales

Any idea on how to achieve this? 

Thanks. 

[CNENFRNL · Accepted solution]

Follow this pattern

4WK = 
VAR __max =
    MINX(
        CALCULATETABLE(
            VALUES( DATES[_yyyywk] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            DATES[_yyyywk] > MAX( DATES[_yyyywk] )
        ),
        DATES[_yyyywk]
    )
VAR __min =
    MINX(
        TOPN(
            3,
            CALCULATETABLE(
                VALUES( DATES[_yyyywk] ),
                REMOVEFILTERS( DATES[YYYYWK] ),
                DATES[_yyyywk] <= MAX( DATES[_yyyywk] )
            ),
            DATES[_yyyywk]
        ),
        DATES[_yyyywk]
    )
RETURN
    CONCATENATEX(
        CALCULATETABLE(
            VALUES( DATES[YYYYWK] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            __min <= DATES[_yyyywk],
            DATES[_yyyywk] <= __max
        ),
        DATES[YYYYWK],
        ", "
    )

[CNENFRNL · Accepted solution]

Follow this pattern

4WK = 
VAR __max =
    MINX(
        CALCULATETABLE(
            VALUES( DATES[_yyyywk] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            DATES[_yyyywk] > MAX( DATES[_yyyywk] )
        ),
        DATES[_yyyywk]
    )
VAR __min =
    MINX(
        TOPN(
            3,
            CALCULATETABLE(
                VALUES( DATES[_yyyywk] ),
                REMOVEFILTERS( DATES[YYYYWK] ),
                DATES[_yyyywk] <= MAX( DATES[_yyyywk] )
            ),
            DATES[_yyyywk]
        ),
        DATES[_yyyywk]
    )
RETURN
    CONCATENATEX(
        CALCULATETABLE(
            VALUES( DATES[YYYYWK] ),
            REMOVEFILTERS( DATES[YYYYWK] ),
            __min <= DATES[_yyyywk],
            DATES[_yyyywk] <= __max
        ),
        DATES[YYYYWK],
        ", "
    )

[Anonymous · Accepted solution]

Thanks. 

I repurposed a part of your code to get exactly what I needed. 

SUMX(
        TOPN(
            4,
            CALCULATETABLE(
                VALUES('Date'[YearWeek]),
                REMOVEFILTERS('Date'[YearWeek]),
                (INT('Date'[YearWeek])+1) < MAX('Date'[YearWeek])
            ),
            'Date'[YearWeek],DESC
        ),
        [Sales]
    )

[vanessafvg]

do you have a proper date and date table to work with?

[Anonymous]

Yes I have one. 

Yes I have a one. 

Please keep in mind that the week is Monday to Sunday. 

Thanks.