Last 3 Historical values and dates

[Gryphon269]

Hello,

I have a table that shows the activity that our animals go through. It shows things like vaccinations and weight recorded during that activity.

I am trying to get this table into a matrix that shows the last weight, second to last weight, and third to last weight along with their respective dates. There are more animals as well as other columns, and since the animal is not weighed every time in creates blanks in the table.

Current table:

ActivityDate	AnimalID	Weight
5/16/2018	6704
9/26/2018	6704	460
10/10/2018	6704	420
3/12/2019	6704	650
3/13/2019	6704
7/8/2019	6704
7/9/2019	6704	750
7/10/2019	6704
9/16/2019	6704
5/29/2020	6704	810
10/23/2020	6704	940
5/19/2021	6704
9/29/2021	6704	920
4/27/2022	6704	830
9/9/2022	6704	1000
12/28/2022	6704
4/21/2023	6704	806
9/14/2023	6704
10/5/2023	6704	834
11/15/2023	6704
4/29/2024	6704	850
9/11/2024	6704	984

What i want the matrix to look like when 9/11 is selected(or is maxdate):

ActivityDate	AnimalID	LastWeight Date	LastWeight	LastWeight-1 Date	LastWeight-1	LastWeight-2 Date	LastWeight-2
9/11/2024	6704	9/11/2024	984	4/29/2024	850	10/5/2023	834

I can the the "LastWeight" well enough using lastnonblank(weight,distinctcount(animalID)), but I am have issues getting the date and other values into measures. 

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[ThxAlot · Accepted solution]

Last N Date = 
SELECTCOLUMNS(
    INDEX(
        MAX( SLC[Value] ),
        CALCULATETABLE(
            SUMMARIZE( DATA, DATA[ActivityDate] ),
            NOT ISBLANK( DATA[Weight] )
        ),
        ORDERBY( DATA[ActivityDate], DESC )
    ),
    DATA[ActivityDate]
)

Last N Weight = 
SELECTCOLUMNS(
    INDEX(
        MAX( SLC[Value] ),
        CALCULATETABLE(
            SUMMARIZE( DATA, DATA[ActivityDate], DATA[Weight] ),
            NOT ISBLANK( DATA[Weight] )
        ),
        ORDERBY( DATA[ActivityDate], DESC )
    ),
    DATA[Weight]
)

[parry2k]

@Gryphon269 here is the example output:

[parry2k]

@Gryphon269 you can do something like this:

Last Weight = 
CALCULATE ( 
    MAX ( 'Animal'[Weight] ),
    INDEX ( 
        -1,
        FILTER ( ALL ( 'Animal'[ActivityDate], Animal[AnimalID], Animal[Weight] ), NOT ISBLANK ( Animal[Weight] ) ),
        ORDERBY ( Animal[ActivityDate] ),
        PARTITIONBY ( Animal[AnimalID] )
    )
)

Last Weight - 1 = 
CALCULATE ( 
    MAX ( 'Animal'[Weight] ),
    INDEX ( 
        -2,
        FILTER ( ALL ( 'Animal'[ActivityDate], Animal[AnimalID], Animal[Weight] ), NOT ISBLANK ( Animal[Weight] ) ),
        ORDERBY ( Animal[ActivityDate] ),
        PARTITIONBY ( Animal[AnimalID] )
    )
)
Last Weight - 2 = 
CALCULATE ( 
    MAX ( 'Animal'[Weight] ),
    INDEX ( 
        -3,
        FILTER ( ALL ( 'Animal'[ActivityDate], Animal[AnimalID], Animal[Weight] ), NOT ISBLANK ( Animal[Weight] ) ),
        ORDERBY ( Animal[ActivityDate] ),
        PARTITIONBY ( Animal[AnimalID] )
    )
)

Date Last Weight = 
CALCULATE ( 
    MAX ( 'Animal'[ActivityDate] ),
    INDEX ( 
        -1,
        FILTER ( ALL ( 'Animal'[ActivityDate], Animal[AnimalID], Animal[Weight] ), NOT ISBLANK ( Animal[Weight] ) ),
        ORDERBY ( Animal[ActivityDate] ),
        PARTITIONBY ( Animal[AnimalID] )
    )
)