cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GabrielaSofia Helper I
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 IDCategoryDate

A

X10-11-2019

A

Y30-11-2019

B

Y01-11-2019
BX07-11-2019

C

X10-11-2019
CY18-11-2019
CY28-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
Super User IV

Re: Summarize by LAST date category

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





Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
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
Community Support

Re: Summarize by LAST date category

Hi @GabrielaSofia ,

 

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.
5-1.PNG

Here is my test file for your reference.

 

Community Support Team _ Eads
If this post helps, then please consider Accept it as the solution to help the other members find it.
GabrielaSofia Helper I
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,

Thank you for your time,
Best regards,
Gabriela

 

 

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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