Forum Discussion

Gryphon269's avatar
Gryphon269
Frequent Visitor
1 year ago
Solved

Last 3 Historical values and dates

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:

ActivityDateAnimalIDWeight
5/16/20186704 
9/26/20186704460
10/10/20186704420
3/12/20196704650
3/13/20196704 
7/8/20196704 
7/9/20196704750
7/10/20196704 
9/16/20196704 
5/29/20206704810
10/23/20206704940
5/19/20216704 
9/29/20216704920
4/27/20226704830
9/9/202267041000
12/28/20226704 
4/21/20236704806
9/14/20236704 
10/5/20236704834
11/15/20236704 
4/29/20246704850
9/11/20246704984

 

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

 

ActivityDateAnimalIDLastWeight DateLastWeightLastWeight-1 Date LastWeight-1LastWeight-2 Date LastWeight-2
9/11/202467049/11/20249844/29/202485010/5/2023834

 

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. 

 

Need Help
  • ThxAlot's avatar
    ThxAlot
    1 year ago
    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]
)

4 Replies

  • parry2k's avatar
    parry2k
    Super User
    1 year ago

    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] )
    )
)
  • ThxAlot's avatar
    ThxAlot
    Super User
    1 year ago
    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]
)