Comparison between last and this month rating when counting unique IDs

[DANIPAR]

Hello,

I have a table with raw data with these colums (Create date, ID (which are unique values), Operator number, Status(COLD or HOT), Qualification (Qualified or Unqualified).

CreatedDate	Id	Operator number	Status	Qualification
2018-03-09 18:01	00Q5800000VGMQzEAP	65005	COLD	Qualified
2018-03-09 18:03	00Q5800000VGMRTEA5	65005	COLD	Unqualified
2018-03-09 18:06	00Q5800000VGMRxEAP	65005	COLD	Qualified
2018-03-09 18:07	00Q5800000VGMS7EAP	65005	COLD	Qualified
2018-03-09 18:09	00Q5800000VGMSbEAP	65005	COLD	Qualified
2018-03-09 18:11	00Q5800000VGMSgEAP	65005	COLD	Unqualified
2018-03-09 18:12	00Q5800000VGMT8EAP	65005	COLD	Qualified
2018-03-09 18:14	00Q5800000VGMTIEA5	65005	COLD	Qualified
2018-03-09 18:15	00Q5800000VGMTcEAP	65005	COLD	Qualified
2018-03-09 18:51	00Q5800000VGMXxEAP	65008	HOT	Qualified
2018-03-10 11:10	00Q5800000VGNxTEAX	65029	COLD	Unqualified
2018-03-10 11:11	00Q5800000VGNxsEAH	65029	COLD	Qualified

My goal is to create such table:

Operator number	Count of IDs	Current month's rating	Current rating vs last months rating
65005	12	1	-2
65029	10	2	0
65075	6	3	+2
…	…	…	…

My goal is to rate operators (by their operator number), while counting IDs and assign a rating + compare current month's rating VS last months rating (to show how many possitions were gained(-)/lost(+)). + filters for Status (HOT) and Qualification (Qualified).

Thank you very much!

[AlB]

@DANIPAR

1. Create a DateT date table to select the month considered "current" and a realship with the fact table

2. Place DateT[YearMonth] in a slicer and select the base month of interest ("current")

3. Place Table1[Operator Number] in a visual table

4. Create these 4 measurements and place them in the visual table

CountId = COUNT(Table1[Id])

Rank Current Month = RANKX(ALL(Table1[Operator number]), [CountId])

Rank Previous Month = 
CALCULATE([Rank Current Month], PREVIOUSMONTH(DateT[Date]))

Current rank vs last months rank =
[Rank Current Month] - [Rank Previous Month]

5. View everything at work in the attachment.

You'll probably have to adjust it a little bit to get what exactly you need, but this should give you a pretty good idea

Please mark the resolved question when you are finished and consider giving a thumbs up if the posts are useful.

Contact me privately for assistance with any large-scale BI needs, tutoring, etc.

Bless you

[AlB]

Hi @DANIPAR 

What exactly is "Current month"? Is it the actual current month (ie now December-20) or is it something you want to specify on a slicer? Any other things to consider?

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[DANIPAR]

Thank you for reply. 

I will some addition - it might sound confusing, but current month actually is past month (e.g. now is December and I will be calculating results for November). So the last month would be October.