Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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],
", "
)
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 still way 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]
)
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],
", "
)
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 still way 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]
)
do you have a proper date and date table to work with?
Proud to be a Super User!
Yes I have one.
Yes I have a one.
Please keep in mind that the week is Monday to Sunday.
Thanks.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |