Date slicer to filter to 2 date columns

[Anonymous]

Hi, 

I would like to use a single slicer to filter 2 columns in the same table. Assuming the table below:

Contract Code; Inception Date; Maturity Date

ABC; 2019-10-03; 

DEF; 2019-10-05; 2020-05-05;

GHI; 2019-10-03; 2019-10-07

From the above i would like to extract the "active" contracts as at a month-end date. 

Assuming from the slicer i choose 2019-10-31 (October Month End). As at end of October ABC and DEF are still active (where Maturity Date is NULL OR Maturity Date > @MonthEnd). So i would like to these 2 as a result.

GHI is no longer active since it matured before end Oct 2019 so this should be excluded. 

Your input is appreciated. 

Thanks

[v-xicai · Accepted solution]

Hi @Anonymous ,

You can create calendar table , put 'Calendar'[Date] into slicer visual to filter the date, then create measure like DAX below.

Calendar =CALENDARAUTO() .
 
CumulativeIDs =
var d=SELECTEDVALUE('Calendar'[Date])
return COUNTROWS(FILTER(Table1,Table1[Maturity Date]=BLANK()||(d<=Table1[Maturity Date]&&d>=Table1[Inception Date])))

Best Regards,

Amy

Community Support Team _ Amy

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