Count of Most Recent entries per case

[mrutherford83]

Casecode	addeddate	PC Level	PC Level Most Recent
Casecode1	8 May 2019	1	1
Casecode 2	8 May 2019	1	3
Casecode 2	3 Oct 2019	2	3
Casecode 2	5 May 2020	3	3
Casecode 3	6 Nov 2017	1	2
Casecode 3	5 May 2020	2	2
Casecode 4	5 May 2020	1	1
Casecode 4	2 Feb 2020	2	1
Casecode 4	15 Dec 2019	1	1
Casecode 4	4 Nov 2019	1	1

I have the above table and I have tried numerous different methods but I cannot figure out how to do this in Power BI. 

I have figured out how to calculate the most recent entry in the "PC Level Most Recent" but the frist part of what I would like to do is add two further columns which will calculate the second and third most recent entries. 

Then what I want to do is provide a summary and outline how many 'cases' are in 1, 2 and 3 currently, and then what level they were at and the entry prior and then the third prior entry so something like this: -

PC Level	Current	Prior	Third Prior
1	2	5	8
2	1	3	4
3	1	2	3

Please note the data in this table does not relate to the above but basically what I want to see if the movement between the count entry on entry.

Hopefully that makes sense, any help is greatly appreciated

[v-juanli-msft]

Hi @mrutherford83 

I'm not clear how to calculate the "PC Level Most Recent".

Is it the last date "PC Level" based on every casecode?

Could you give a sample with corresponding result?

Best Regards

Maggie

[mrutherford83]

Hi @v-juanli-msft 

Thanks for your reply.

Here is an extract from the actual table itself: -

casecode	adddate	PC Level	PC Level Most Recent
Casecode 1	08-May-19	1	1
Casecode 2	08-May-19	1	1
Casecode 2	03-Oct-19	1	1
Casecode 3	05-May-20	1	1
casecode 4	05-May-20	1	1
casecode 4	06-Nov-17	1	1
Casecode 5	12-Dec-18	1	2
Casecode 5	19-Dec-18	2	2
Casecode 6	13-May-20	1	1
Casecode 6	06-Dec-17	1	1
Casecode 7	20-May-20	2	2
Casecode 8	24-Feb-20	2	2
Casecode 8	27-Mar-19	1	2
Casecode 8	13-Mar-19	2	2
Casecode 8	10-Apr-19	2	2
Casecode 9	17-Oct-19	2	2
Casecode 9	07-Oct-19	1	2

and this is the code I used to create the PC Level Most Recent colmun that I added:-

PC Level Most Recent = CALCULATE( MAX( Notes[PC Level]), FILTER( Notes,Notes[casecode]=EARLIER(Notes[casecode])))

Does that help at all?

[d_gosbell]

So your current column is just getting the maxium level within a casecode, it's not looking at the addeddate at all. If I understand what you want to do I think you need to lookup the max date for a given casecode, then find the score for that and do this for the 2 prior addeddate values within a given casecode. If this is correct then I think the following measure might work. It's maybe a little more complex than it needs to be, but I've set it up so you just need to change the value of the _priorNumber to go back 2,3,4 etc dates

Prior 1 = 
var _priorNumber = 1
// store the casecode for the current row
var _currentCase = 'Table'[Casecode]
// get all the dates for this casecode
var _allDates = SELECTCOLUMNS(filter(ALL('Table'), 'Table'[Casecode] = _currentCase),"addeddate",[addeddate])
// Get the min date within the topN
var _recentDate = MINX(TOPN(_priorNumber,_allDates,[addeddate],DESC),[addeddate])
// lookup the level for this date and cosecode
var _result = LOOKUPVALUE('Table'[PC Level],'Table'[addeddate],_recentDate,'Table'[Casecode],_currentCase)
return if(countrows(_allDates) >= _priorNumber, _result)

You can see this working in the screenshot below (note I added some data to your example so I could see in casecode2 that it was not just grabbing the levels in numeric order)