Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
shaunguyver
Helper III
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
Christann
Helper IV
Helper IV

Try something like this:

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

 

View solution in original post

3 REPLIES 3
ChandeepChhabra
Impactful Individual
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

Christann
Helper IV
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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.