Forum Discussion
Get last sequences for a value in date range
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
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
3 Replies
- v-luwang-msft
Community Support
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
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
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
