cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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

@AudiencesQuesti Try something like:

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors