Forum Discussion

shaunguyver's avatar
shaunguyver
Icon for Helper III rankHelper III
7 years ago
Solved

Count instances occurring between fixed date and today

I have a table, 'Risks'.

 

In this table I have a column, 'Date', which gives a range of dates across 2018.

 

I also have a second column, 'Type', which gives the type of event associated with that date. The values are either 'Open' or 'Closed'.

 

I want to create a measure that calculates how many 'Open' events occured since a fixed date '01/01/2018' and Today.

 

I have tried to create this measure but have had no luck. Does anyone have any ideas?

 

Thanks

Need Help
Show and Tell
  • Christann's avatar
    Christann
    7 years ago

    Try something like this:

    OpenEvents =
CALCULATE (
    COUNTA ( Table1[Type] ),
    Table1[Date] >= DATE ( 2018, 1, 1 ),
    Table1[Date] <= TODAY (),
    Table1[Type] = "Open"
)

     

3 Replies

  • rafaelmpsantos's avatar
    rafaelmpsantos
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

    Hi lets try.

    MEASURE = 
    calculate(count(Table[Type]),datesbetween(table[Date],01/01/2018,today())

     

    If it solve please mark as solved and give me your positive feed back.

  • Christann's avatar
    Christann
    Icon for Advocate IV rankAdvocate IV
    7 years ago

    Try something like this:

    OpenEvents =
CALCULATE (
    COUNTA ( Table1[Type] ),
    Table1[Date] >= DATE ( 2018, 1, 1 ),
    Table1[Date] <= TODAY (),
    Table1[Type] = "Open"
)

     

  • ChandeepChhabra's avatar
    ChandeepChhabra
    Icon for Impactful Individual rankImpactful Individual
    7 years ago

    shaunguyver - Hi 

     

    Please create a Calendar Table and relate it to your Risks Table

    Then try this measure

     

    Instances =
    CALCULATE (
    COUNT ( Risks[Date] ),
    DATESBETWEEN ( Calendar[Date], DATE ( 2018, 1, 1 ), TODAY () ),
    Risks[Type] = "Open"
    )

     

    If you would like to have a unique count of the date then replace the COUNT with DISTINCTCOUNT

     

    Hope this helps