cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
powpow11
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:

 

CategorySubcategoryCustomerDateValue
  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-261
  Category2    Subcategory3    Customer3  2001-12-038
  Category1    Subcategory3    Customer2  2001-12-265
  Category1    Subcategory2    Customer2  2002-03-198
  Category3    Subcategory2    Customer2  2002-05-1313
  Category2    Subcategory3    Customer2  2002-07-201
  Category2    Subcategory2    Customer2  2002-08-1612
  Category3    Subcategory1    Customer1  2002-08-181
  Category1    Subcategory2    Customer1  2002-09-283
  Category2    Subcategory3    Customer1  2002-12-2611
  Category1    Subcategory3    Customer2  2003-03-172
  Category2    Subcategory1    Customer3  2003-06-254
  Category1    Subcategory3    Customer3  2003-07-236
  Category2    Subcategory1    Customer3  2003-09-2110
  Category2    Subcategory1    Customer1  2003-10-231
  Category2    Subcategory3    Customer2  2004-01-304

 

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?
 

Annotation 2020-07-21 192858.jpg

 

 

Thanks

3 REPLIES 3
Greg_Deckler
Super User
Super User

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


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop!
Microsoft Hates Greg's Quick Measures
Check out my latest book!

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

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!