Get last sequences for a value

[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-yangliu-msft · Accepted solution]

Hi  @ldmass ,

Here are the steps you can follow：

1. Create calculated table.

Date =
CALENDAR( DATE(2022,1,1),DATE(2022,12,31))

2. Create measure.

Flag =
var _mindate=MINX(ALLSELECTED('Date'),[Date])
var _maxdate=MAXX(ALLSELECTED('Date'),[Date])
var _max=
MAXX(FILTER(ALLSELECTED('Table'),
'Table'[Date]>=_mindate&&'Table'[Date]<=_maxdate&&'Table'[Sinister]=MAX('Table'[Sinister])),[Sequence])
return
IF(
    MAX('Table'[Sequence])=_max,1,0)

3. Place [Flag]in Filters, set is=1, apply filter.

4. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly