cancel
Showing results for
Did you mean:
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
Super User

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

 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!
Regular Visitor

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]
)
4 REPLIES 4
Super User

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

 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!
Regular Visitor

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

Regular Visitor

Yes I have one.

Yes I have a one.

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

Thanks.

Announcements

#### 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.

#### 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!

#### 2022 Monthly Feature Releases

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

Top Solution Authors
Top Kudoed Authors