How to get sum of sales from Max weeks in a quarter

AudiencesQuesti — Thu, 17 Aug 2023 18:09:11 GMT

Hi,

I have a table with quarters and weeks, where the sales is a snapshot of the sales at x weeks into the quarter (cumulative). I'd like to get a sum of all sales filtering only for the latest weeks in the quarter (lines in red below).

The issue I'm running into is that the Max-Weeks formula I use tends to only capture the absolute max of all weeks and ignores the max weeks per quarter. The formula I'm working with is below. Any help?

Qtr	Weeks	Sales
Q1	1	1000
Q1	2	1200
Q1	3	1500
Q1	4	1600
Q2	1	500
Q2	2	600
Q2	3	800
Q2	4	1000
Q3	1	200
Q3	2	400
Q3	3	600
Q3	4	800
Q4	1	200
Q4	2	300

Measure =

var MaxWk = MAX(Table[Weeks])

return

CALCULATE(

SUM(Table[Sales]),

Weeks = MaxWk)

Re: How to get sum of sales from Max weeks in a quarter

Greg_Deckler — Thu, 17 Aug 2023 18:23:43 GMT

AudiencesQuesti Try something like:

Measure = VAR __Table = SUMMARIZE( 'Table', [Qtr], "__MaxWeek", MAX( 'Table'[Week] ) ) VAR __Table1 = ADDCOLUMNS( __Table, "__Sales", VAR __Qtr = [Qtr] VAR __Result = SUMX(FILTER('Table', [Qtr] = __Qtr && [Week] = [__MaxWeek]), [Sales]) RETURN __Result ) VAR __Result = SUMX( __Table1, [__Sales] ) RETURN __Result

topic Re: How to get sum of sales from Max weeks in a quarter in DAX Commands and Tips

How to get sum of sales from Max weeks in a quarter

Re: How to get sum of sales from Max weeks in a quarter