cancel
Showing results for
Did you mean:
New Member

## Dynamically graphing events based on dates

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?

1 ACCEPTED SOLUTION
New Member

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

4 REPLIES 4
Super User

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?

Super User

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.

New Member

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

Super User

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

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

#### What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors
Top Kudoed Authors