Dynamically graphing events based on dates

[dlix]

Hello,

I have a table listing a number of events with the corresponding start and end dates and other attributes, e.g.:

id	start_date	end_date	country	type	public
e01	2021/03/13	2021/07/19	Germany	Exhibition	yes
e02	2021/05/15	2021/06/18	France	Market	yes
e03	2021/09/23	2021/11/20	Spain	Market	yes
e04	2021/04/22	2021/08/29	Italy	Exhibition	no
e05	2021/06/19	2021/09/25	Spain	Market	yes
e06	2021/09/14	2021/09/23	Germany	Exhibition	yes
e07	2021/08/18	2021/10/30	Germany	Committee	no
e08	2021/08/27	2021/11/15	France	Exhibition	no
e09	2021/10/16	2021/11/17	Italy	Committee	yes
e10	2021/06/20	2021/08/22	France	Committee	no

I need to create a graph of the number of events active in each of the last 12 months, something like this:

I'm struggling to find a suitable way to create the graph: I am not able to create an intermediate static table with DAX because I need to allow filtering of the data based on several attributes and to drill down to the actual event data. To create the example I just duplicate each event line for each of the months it lasts, but I can't use the same strategy with the real data because I have tens of thousands of events each spanning several months and with tens of attributes and I don't really want to deal with half million records.

Is there a clever way to count the events in each histogram bar based on an arbitrary condition? I mean, each bar should basically count the events where:

start_date <= end_of_month AND end_date >= start_of_month

but I can't find how to implement it. Are there any other way to solve this problem?

Thanks in advance

[lbendlin]

You're on the right track. You need to generate intermediate tables, but you need to do that inside your measure. For each month of your x axis value you need to compute if the event was active in that month.  This requires a disconnected table for your x axis value.  The problem with this approach is that you have to hard code the measure for each attribute's values.

"but I can't use the same strategy with the real data because I have tens of thousands of events each spanning several months and with tens of attributes and I don't really want to deal with half million records."

The number of attributes will not impact the number of rows, it will just widen the table.  If you have tens of thousands of events and they span over ten months each then you only end up with hundred thousand rows.  Even 500K rows is still a reasonably small number.  Plus, in this scenario you can do all the prep work in Power Query so you only have to do it once per refresh.

Is that sufficient to get you going or would you like to see a sample implementation?

[lbendlin]

I thought aboutit some more - it might be better to separate both tables - the event header table and the eventmonths table. That way you save on storage and can do easier reporting on header level.

[dlix · Accepted solution]

Thanks, that's an interesting approach. This can minimize the space used to store the data and would keep the initial table "clean" for other calculations.

Playing around with measures, I think I've found a possible solution using just the original data:

Count of id in month = 
VAR __SELECTED = SELECTEDVALUE( 'months'[month])
VAR __MONTH_START = DATE( YEAR( __SELECTED), MONTH( __SELECTED), 1)
VAR __MONTH_END = EOMONTH( __SELECTED, 0)
RETURN
    CALCULATE( COUNTA( 'events'[id]),
        'events'[start_date] <= __MONTH_END, 'events'[end_date] >= __MONTH_START
    )

It needs a "months" table with a list of the nonths to be represented in the chart:

month

2021/03/01

2021/04/01

2021/05/01

(etc...)

This seems quite fast and flexible as I can easily create different measure for different views (e.g. events starting and ending in the same month, new events for the month, etc.), but I'm not sure if it solid enough or if it will bring any issue while I proceed with the implementation.

One problem my solution has compared to your proposal is that it returns just a single value for each data point so I can't use the "show data point as a table" menu or navigate the data.

I will probably try to implement both on the real data and see which one suits better the reporting objectives.

Thanks a lot

[lbendlin]

One other issue with the measure approach is that it needlessly recomputes all the time. Your data is immutable and doesn't need recomputing.