cancel
Showing results for
Did you mean:
Helper I

## Summarize by LAST date category

Hi! I have a table of Sales with Products, Categories and Dates of the sale, like this:

 Product ID Category Date A X 10-11-2019 A Y 30-11-2019 B Y 01-11-2019 B X 07-11-2019 C X 10-11-2019 C Y 18-11-2019 C Y 28-11-2019

The thing is, I need to count how many products belong to a category, but the product can change its category in time, so we use this criterion: the Product belongs to the Category by which it was sold at the end of the given period (usually I visualize this month by month but could be by year too).

So for example, if I want to know how many products were X on November, I can make a DISTINCTCOUNT of the products by month filtered by category: that will give me a 3 (A,B, C) but it should be 1 (just B), and if I count the Y products on November I would also get a 3, but it should be a 2.

Why do we need this LAST category standard? because this DISTINCTCOUNT gives me a total of 3 Products on X and 3 on Y, which is a contradiction, because I don't have 6 products, I only have A, B and C.
It also mess up things when I need to make more advanced calculations, like for example "How many products were sold just once on november?" if I make the Measure and then filter by category, taking the Y out, A B and C would appear, which is false because the three of them were sold more than once.

This has been a really tricky problem and I don't know how to solve it, I've tried by myself and with the help of two consultants, no one could give me a solution neither with Calculated Columns nor with Measures. I know there must be at least one person with the same problem that already solved it or some Power BI expert in this great Community, come out wherever you are please! I would very much appreciate your help,

Best regards,
Gabriela Sofía

3 REPLIES 3
Super User IV

## Re: Summarize by LAST date category

https://community.powerbi.com/t5/Desktop/Max-latest-value-per-category-based-on-slicer-value/td-p/49...

https://community.powerbi.com/t5/Desktop/Retrieve-last-entry-by-group/td-p/584321

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

In case it does not help, please provide additional information and mark me with @
My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP,
HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Proud to be a Super User! Linkedin

Community Support

## Re: Summarize by LAST date category

You may create a measure to calculate the count.

``````count =
VAR a =
ALLEXCEPT ( 'Table', 'Table'[Product ID] )
RETURN
CALCULATE (
DISTINCTCOUNT ( 'Table'[Product ID] ),
a,
ALLSELECTED ( 'Table'[Category] ),
FILTER ( 'Table', 'Table'[Date] = CALCULATE ( MAX ( 'Table'[Date] ), a ) )
)``````

Here is the result.

Here is my test file for your reference.

If this post helps, then please consider Accept it as the solution to help the other members find it.
Helper I

## Re: Summarize by LAST date category

Hi! everyone, thanks for the interest in this issue.
@amitchandak I tried your references but they're not that related to my problem

@v-eachen-msft  I tried your solution but it doesn't work when I try it with my data, as I go back in time I only get zeros, so in the last month I have a big count and on the months from a year ago I have no count, I don't understand why that happens, maybe we could talk more about it internally so I can give you more information to fully understand the problem,

Best regards,
Gabriela

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Top Solution Authors
Top Kudoed Authors