cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Helper III

## 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

1 ACCEPTED SOLUTION
Helper IV

Try something like this:

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

3 REPLIES 3
Impactful Individual

@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

Helper IV

Try something like this:

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

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.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors