Forum Discussion

Nickb167's avatar
Nickb167
Frequent Visitor
5 years ago
Solved

Getting value for second last date in related table.

Hi,

 

I have a data set that appears as below:

IDDateScore
11/01/20203
31/01/20202
41/01/20205
61/01/20204
12/02/20202
32/02/20203
42/02/20205
62/02/20201
13/03/20205
33/03/20203
43/03/20204
63/03/20201
14/04/20202
34/04/20205
44/04/20202
64/04/20204

 

The expected result is:

 

IDFirst ScoreLatest ScoreSecond Latest Score
1325
3253
4524
6441

 

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
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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))

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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