Get last sequences for a value in date range

[ldmass]

Hi Experts,
I'm having trouble solving an issue that seems simple but I can't find the solution. I have a table where I have the following columns:

Sinister	Sequence	Date	Patient	Episode	Type of Coverage
1000	1	01-01-2022	Juan	1	2
1000	2	05-01-2022	Juan	2	4
1000	3	02-02-2022	Juan	3	1
1000	4	15-02-2022	Juan	4	4
1001	1	02-02-2022	Pedro	1	4
1001	2	15-01-2022	Pedro	4	2
1001	3	02-02-2022	Pedro	5	4
1000	5	01-03-2022	Juan	4	8

I need for a range of dates to show the last Sequence (Column B) for the same Sinister (Column A), and then filter by Type of Coverage (Column F). For example:
Example 1:
If in the date slicer I select 01-01-2022 to 01-31-2022, only row 4 should be displayed in the matrix/table. Why?
- For Sinister 1000, the last sequence is 2 and type of coverage is 4
- For Sinister 1001, the last sequence is 2 but the Type of Coverage is 2.

Example 2:
If in the date slicer I select 01-01-2022 to 28-02-2022, only row 5 and 8 should be displayed in the matrix/table. Why?
- For Sinister 1000, the last sequence is 4 and type of coverage is 4.
- For Sinister 1001, the last sequence is 3 and type of coverage is 4.

Any help is welcome.

Greetings,

LM

[v-luwang-msft]

HI  @ldmass ,

Pls use the below measure ,then filter the measure:

TEST = 
IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )
        = MAX ( 'Table'[Date] ),
    1,
    0
)

Output result:(the second slicer ,with two same last date on 1001,so with the two result)

Best Regards

Lucien

[ldmass]

Hi Lucien,

Thanks you for your answer. In the second image, only sequence 3 should be displayed, since it is the largest, regardless of the date. For the selected date range, only the largest sequence should be displayed

[v-luwang-msft · Accepted solution]

HI  @ldmass ,

Adjust to the below:

TEST = var maxdate=CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )  var pd1= IF (
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )
                && 'Table'[Date] >= CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
                && 'Table'[Date] <= CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
        )
    )
        = MAX ( 'Table'[Date] ),
    1,
    0
) var pd2=IF(MAX('Table'[Sequence])=
CALCULATE (
        MAX ( 'Table'[Sequence]),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Sinister] = MAX ( 'Table'[Sinister] )&&'Table'[Date]= maxdate)),1,0)  return  IF(PD1=1&&pd2=1,1,0)

Result:

Best Regards

Lucien