cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Olanrewajusanni
Regular Visitor

Last 4 Weeks Total (minus previous week)

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. 

2 ACCEPTED SOLUTIONS
CNENFRNL
Super User
Super User

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_0-1669578949824.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

View solution in original post

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]
    )

View solution in original post

4 REPLIES 4
CNENFRNL
Super User
Super User

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_0-1669578949824.png


Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is beyond their comprehension!

DAX is simple, but NOT EASY!

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
Super User
Super User

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





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Yes I have one. 

Untitled.pngYes I have a one. 

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

Thanks. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

Thank you 2022 Review

2022 Monthly Feature Releases

We had a great 2022 with a ton of feature releases to help you drive a data culture.