Forum Discussion
Helper IGet records between start and end date using a slicer
Hello,
I have a table with this structure:
| Name | Start date | End date |
| A | 01/02/2020 | 25/02/2021 |
| B | 02/06/2020 | 01/01/2021 |
| C | 01/05/2020 | |
| D | 20/04/2020 | 31/12/2020 |
Date format is dd/mm/yyyy
I need to pick a date from a slicer, and based on that date I must retrieve all the records that were active on that date (picked date between start and end date, both included). Records with empty end date are still active.
Examples:
10/2/2020
I should get record: A
1/1/2021
I should get records: A, B, C
1/5/2020
I should get records: A, C, D
I don't know how to do this.
Hi Terrassa,
Depending on how you want to list the final output, you may need one measure plus additional filter linked to this measures or two measures below. Check the attached PBIX file for more details.
filterMeasure = VAR startDate = MAX ( dataTbl[Start date] ) VAR endDate = MAX ( dataTbl[End date] ) VAR selectedDate = MAX ( calendarTbl[Date] ) VAR conditionOne = SWITCH ( TRUE(), ISBLANK ( startDate ), TRUE(), startDate <= selectedDate, TRUE(), FALSE() ) VAR conditionTwo = SWITCH ( TRUE(), ISBLANK ( endDate ), TRUE(), endDate >= selectedDate, TRUE(), FALSE() ) RETURN IF ( conditionOne && conditionTwo, 1, 0 )listedNames = VAR _tbl = ADDCOLUMNS ( dataTbl, "flag", [filterMeasure] ) RETURN CONCATENATEX ( FILTER ( _tbl, [flag] = 1 ), [Name], ", " )Best Regards,
Alexander
1 Reply
- barritown
Solution Sage
Hi Terrassa,
Depending on how you want to list the final output, you may need one measure plus additional filter linked to this measures or two measures below. Check the attached PBIX file for more details.
filterMeasure = VAR startDate = MAX ( dataTbl[Start date] ) VAR endDate = MAX ( dataTbl[End date] ) VAR selectedDate = MAX ( calendarTbl[Date] ) VAR conditionOne = SWITCH ( TRUE(), ISBLANK ( startDate ), TRUE(), startDate <= selectedDate, TRUE(), FALSE() ) VAR conditionTwo = SWITCH ( TRUE(), ISBLANK ( endDate ), TRUE(), endDate >= selectedDate, TRUE(), FALSE() ) RETURN IF ( conditionOne && conditionTwo, 1, 0 )listedNames = VAR _tbl = ADDCOLUMNS ( dataTbl, "flag", [filterMeasure] ) RETURN CONCATENATEX ( FILTER ( _tbl, [flag] = 1 ), [Name], ", " )Best Regards,
Alexander