cancel
Showing results for
Did you mean:
New Member

## Sum of sales for earliest date per category

Hi everyone, I have been struggling with a calcualtion for a while now, maybe someone can help.

I have my data as follows:

 Category Subcategory Customer Date Value Category1 Subcategory1 Customer1 2001-01-19 9 Category2 Subcategory3 Customer3 2001-04-20 7 Category3 Subcategory2 Customer2 2001-06-29 11 Category3 Subcategory3 Customer1 2001-09-23 13 Category1 Subcategory3 Customer1 2001-10-26 1 Category2 Subcategory3 Customer3 2001-12-03 8 Category1 Subcategory3 Customer2 2001-12-26 5 Category1 Subcategory2 Customer2 2002-03-19 8 Category3 Subcategory2 Customer2 2002-05-13 13 Category2 Subcategory3 Customer2 2002-07-20 1 Category2 Subcategory2 Customer2 2002-08-16 12 Category3 Subcategory1 Customer1 2002-08-18 1 Category1 Subcategory2 Customer1 2002-09-28 3 Category2 Subcategory3 Customer1 2002-12-26 11 Category1 Subcategory3 Customer2 2003-03-17 2 Category2 Subcategory1 Customer3 2003-06-25 4 Category1 Subcategory3 Customer3 2003-07-23 6 Category2 Subcategory1 Customer3 2003-09-21 10 Category2 Subcategory1 Customer1 2003-10-23 1 Category2 Subcategory3 Customer2 2004-01-30 4

I am trying to build a measure to summarize sales from the earliest available data for each Category/Subcategory/Customer respectively.

The measure ValuFromEarliest = CALCULATE( sum ( data[Value] ) , filter ( data , data[Date] = min ( data[Date] ) ) )
gets the individual values right, but not the subtotals, as it can be seen below. How can I calculate a measure that works for the subtotals as well?

Thanks

3 REPLIES 3
Super User IV

@powpow11 Looks like you want a different calculation depending on your level in the hierarchy. That's what MM3TR&R was designed to achieve: https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

New Member

Hi @amitchandak & @Greg_Deckler , thanks for the responses.

I coulnd't find a way to get this done with FIRSTNONBLANKVALUE, but researching this further.

As to solving the problem with a dynamically different calculation, it sure is feasible. While I am still hoping to find a way to somehow solve this with a less hardcoded approach, without running a different calc at different levels of the hierarchy, this is definitely a way to get this done.

Super User IV

@powpow11 , Firstnonblankvalue can help. Refer usage in my blog

https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

Proud to be a Super User!

Announcements

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.