Number of products with a rating upgrade/downgrade by Product Groups

[Anonymous]

Hello,
I have a table with Product Group (A and B), Product Names (Product1, Product2 and Product3) and Client Ratings (1=low, 2=medium, 3=good) for each product over several months (3 months in the sample table below)

Product Group	Product	Client Rating	Rating Date
GroupA	Product1	1	31/10/2020
GroupA	Product1	1	30/11/2020
GroupA	Product1	2	31/12/2020
GroupA	Product2	2	31/10/2020
GroupA	Product2	2	30/11/2020
GroupA	Product2	3	31/12/2020
GroupB	Product3	2	31/10/2020
GroupB	Product3	1	30/11/2020
GroupB	Product3	1	31/12/2020

I'm trying to calculate the number of products being upgraded (increase in rating) and downgraded (lower rating) on a reference date vs a comparison date with both dates being selected by the user.

The result for Reference date 31/12/2020 and Comparison date 31/10/2020 should look like this:

Product Group	NumUP	NumDOWN
GroupA	2	0
GroupB	0	1

Thank you

[amitchandak]

@Anonymous ,

Try like these formula. Assume you have date table. You might use all or allselected in filter on date table

Group	NumUP =
 var _min = minxx(allselected(Date),Date[Date])
 var _max = minxx(allselected(Date),Date[Date])
 return 
 countx(filter(summarize(Table, Table[Group] ,Table[Product], "_1", calculate(max(Table[Client Rating]),filter(Date,Date[Date] =_min)),, "_2", calculate(max(Table[Client Rating]),filter(Date,Date[Date] =_max))),[_1]>[_2]),[Product])
 
 
 
  Group	NumDOWN =
 var _min = minxx(allselected(Date),Date[Date])
 var _max = minxx(allselected(Date),Date[Date])
 return 
 countx(filter(summarize(Table, Table[Group] ,Table[Product], "_1", calculate(max(Table[Client Rating]),filter(Date,Date[Date] =_min)),, "_2", calculate(max(Table[Client Rating]),filter(Date,Date[Date] =_max))),[_1]>[_2]),[Product])

[lbendlin]

Nearly there, just need to fix the totals.

First, create an unconnected calculated table that feeds the slicer

Slicer = VALUES(Ratings[Rating Date])

Then use something similar to this

Apart from the totals being wrong this also suffers from the uncertainty of what to do when a product does not have a rating value on one of the selected dates.