Summarize by LAST date category

[Anonymous]

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

[v-eachen-msft]

Hi @Anonymous ,

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.

[Anonymous]

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,

Thank you for your time,
Best regards,
Gabriela

[amitchandak]

Please refer

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