Sum of sales for earliest date per category

[powpow11]

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

[Greg_Deckler]

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

[powpow11]

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.

[amitchandak]

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