Count events beetwen tow dates grouped by a column

[SebaSpotti]

Hi all!

I have a database of events like this:

Place	EventID	Event Start	Event End
STREET	1	01/08/2024 00:00:00	02/08/2024 00:00:00
STREET	2	10/08/2024 00:00:00	13/08/2024 00:00:00
ROAD	3	01/08/2024 00:00:00	15/08/2024 00:00:00
HIGHWAY	4	27/08/2024 00:00:00	30/08/2024 00:00:00
STREET	5	01/08/2024 00:00:00	30/08/2024 00:00:00
HIGHWAY	5	10/08/2024 00:00:00	27/08/2024 00:00:00

and a table of the dates of months.

I want to calculate how many events are "opened" per each day of the calendar and also have the possibility to filter them per "Place".

I tryed to create a table like this:

DateTableShips = UNION(

ADDCOLUMNS (

    CALENDAR (DATE(2024, 9, 1), DATE(2024, 9, 30)),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Place", "STREET"),

ADDCOLUMNS (

    CALENDAR (DATE(2024, 9, 1), DATE(2024, 9, 30)),

    "Year", YEAR([Date]),

    "Month", MONTH([Date]),

    "Place", "ROAD")

)

and it works, but if i want to calculate an average per day, for example, the results its obviously lower then what i want with a simple average. 

Any suggestions?

[DataNinja777]

Hi @SebaSpotti ,

You can achieve the desired output by calculating the duration of open events using a disconnected calendar table and writing the measures as shown below.

Open Events = 
SUMX (
    'Table',
    IF (
        'Table'[Event Start] <= MAX('Calendar'[Date]) &&
        'Table'[Event End] >= MAX('Calendar'[Date]),
        1,
        BLANK()
    )
)

Average open events = averagex('Calendar',[Open events])

I have attached an example pbix file.  Please let me know if this meets your requirements.

Best regards,

[SebaSpotti · Accepted solution]

Thanks, with a little correction it works, the code fore Open Events should be this:

Open Events = 
SUMX (
    'Table',
    IF (
        'Table'[Event Start] <= MIN('Calendar'[Date]) &&
        'Table'[Event End] >= MAX('Calendar'[Date]),
        1,
        BLANK()
    )
)