Get records between start and end date using a slicer

[Terrassa]

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.

[barritown · Accepted solution]

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

My YouTube vlog in English

My YouTube vlog in Russian