Getting value for second last date in related table.

[Nickb167]

Hi,

I have a data set that appears as below:

ID	Date	Score
1	1/01/2020	3
3	1/01/2020	2
4	1/01/2020	5
6	1/01/2020	4
1	2/02/2020	2
3	2/02/2020	3
4	2/02/2020	5
6	2/02/2020	1
1	3/03/2020	5
3	3/03/2020	3
4	3/03/2020	4
6	3/03/2020	1
1	4/04/2020	2
3	4/04/2020	5
4	4/04/2020	2
6	4/04/2020	4

The expected result is:

ID	First Score	Latest Score	Second Latest Score
1	3	2	5
3	2	5	3
4	5	2	4
6	4	4	1

I have worked out the First and Latest Scores with:

Latest Score = SUMX(

VALUES(cnsw_carersstar[cnsw_ProgramParticipant]),

CALCULATE(MAX(cnsw_carersstar[CarersStarScore]), LASTDATE(cnsw_carersstar[DateOnly]))

)

But am having difficulity getting the second latest. I have tried adding filters to the above but cannot seem to get the expected result. Any help would be greatly appreciated.

Thanks,

Nick

[Anonymous · Accepted solution]

Hi @Nickb167 ,

According to my understand, you want to display Score based on the latest date ,the second latest date and the first date, right?

You could use the following formula:

rank =
RANKX (
    FILTER (
        ALL ( LastDateTable ),
        'LastDateTable'[ID] = MAX ( 'LastDateTable'[ID] )
    ),
    CALCULATE ( MAX ( ( LastDateTable[Date] ) ) ),
    ,
    ASC
)

First Score =
CALCULATE ( MAX ( LastDateTable[Score] ), FILTER ( LastDateTable, [rank] = 1 ) )

Last Score =
CALCULATE (
    MAX ( 'LastDateTable'[Score] ),
    FILTER ( 'LastDateTable', [rank] = MAXX ( ALL ( LastDateTable ), [rank] ) )
)

Second Last Score =
CALCULATE (
    MAX ( 'LastDateTable'[Score] ),
    FILTER ( 'LastDateTable', [rank] = MAXX ( ALL ( LastDateTable ), [rank] - 1 ) )
)

My visualization looks like this:

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[Anonymous]

Hi @Nickb167 ,

I think the below table is what you want:

I add an index column in the table, and the measures are:

First Score = CALCULATE(SUM('Table (2)'[Score]), FILTER(ALL('Table (2)'[ID],'Table (2)'[Index]),'Table (2)'[Index] = MIN('Table (2)'[Index])))

Last Score = CALCULATE(SUM('Table (2)'[Score]), FILTER(ALL('Table (2)'[ID],'Table (2)'[Index]),'Table (2)'[Index] = MAX('Table (2)'[Index])))

Second Last Score = CALCULATE(SUM('Table (2)'[Score]), FILTER(ALL('Table (2)'[ID],'Table (2)'[Index]),'Table (2)'[Index] = MAXX(FILTER('Table (2)','Table (2)'[Index] < MAX('Table (2)'[Index])),'Table (2)'[Index])))

Table:

Please try.

Aiolos Zhao

[amitchandak]

@Nickb167 ,

Try

last = lastnonblankvalue(Table[Date], sum(Table[Score]))
Second last =
var _max = maxx(allselected(Date[Date]), Date[Date])
return
calculate(lastnonblankvalue(Table[Date], sum(Table[Score])), filter(allselected(Table), Table[Date] <_max))


First = Firstnonblankvalue(Table[Date], sum(Table[Score]))
Second  =
var _max = maxx(allselected(Date[Date]), Date[Date])
return
calculate(Firstnonblankvalue(Table[Date], sum(Table[Score])), filter(allselected(Table), Table[Date] <_max))