Forum Discussion

antbob's avatar
antbob
Frequent Visitor
7 years ago
Solved

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

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

Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    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

5 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Icon for Community Support rankCommunity Support
    7 years ago

    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's avatar
      antbob
      Frequent Visitor
      7 years ago

      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's avatar
    Anonymous
    Not applicable
    7 years ago

    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.

     

  • Waxy's avatar
    Waxy
    Frequent Visitor
    5 years ago

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

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Icon for Super User rankSuper User
      5 years ago

      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.