Count active contracts which are within date slicer range based on separate start/end date columns

[antbob]

Looking for some help with a measure I am working on and can't seem to figure out.  

I have a contract table with contractid, startdate, enddate columns.  I need to count contracts that were/are active at any point during a date range specificed on the reports date range slicer.

I have tried approached including datesbetween, IF statements, and Filters, but am stuck.  Any help is much appreciated.

contractid	Startdate	Enddate
123456	11/01/2017	11/31/2018
112233	12/15/2017	03/03/2018
144523	01/01/2018	01/01/2019

So If the date range slicer was set for 11/05/2017 - 01/05/2018 the count would show 2

[Waxy]

In addition to having the number of contracts for the date range, how to show the list of contract available?

[Ashish_Mathur]

Hi,

My suggestion would be to convert the 3 column dataset into a 2 column dataset - ContractID and Date.  Then build a Calendar Table with a relationship.  That way when one selects a data range (built from the Calendar Table) in a slicer, only those ContractID's would show up.

[v-frfei-msft · Accepted solution]

Hi @antbob ，

Here we can create a measure as below to get the excepted result we need.

Measure =
CALCULATE (
COUNTROWS ( Table1 ),
FILTER ( Table1, Table1[Startdate] > MIN ( 'Table'[Date] ) )
)

Please find the pbix as attached.

Regards,
Frank

[antbob]

Thanks @v-frfei-msft,

This is certainly closer then I was able to get, and spawned a light bulb moment of understanding.  The end goal is to be able to say how many contracts were active say in the last 3 months, or even the last 6 months given variable length contracts

Here is what I ended up with, which I have yet to fully validate, but a few quick spot checks seemed accurate

Measure = CALCULATE ( 
COUNTROWS(Table1 ),
FILTER( Table1, ( Table1[StartDate] >= MIN ( Table[Date] ) && Table1[StartDate] < MAX ( Table[Date] ) )||( Table1[EndDate] > MIN ( Table[Date]) && Table1[EndDate] <= MAX ( Table[Date] ) )||( Table1[StartDate] < MIN ( Table[Date]) && Table1[EndDate] > MAX ( Table[Date] ) ) ) )

[Anonymous]

here is my suggestion to tackle this :

first Make a new date table.

DateTable = Calendar(Minx(table1,Table1[Date]),Now()) 

Now add a new calculated column:

DateSlicer = CALCULATE(
    COUNTROWS(contractid),
    FILTER(Table1,
        DateTable[Date] >= [startdate] &&
        DateTable[Date] <= [Enddate])

Hope this  will help.