cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
dlix
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.:

 

idstart_dateend_datecountrytypepublic
e012021/03/132021/07/19GermanyExhibitionyes
e022021/05/152021/06/18FranceMarketyes
e032021/09/232021/11/20SpainMarketyes
e042021/04/222021/08/29ItalyExhibitionno
e052021/06/192021/09/25SpainMarketyes
e062021/09/142021/09/23GermanyExhibitionyes
e072021/08/182021/10/30GermanyCommitteeno
e082021/08/272021/11/15FranceExhibitionno
e092021/10/162021/11/17ItalyCommitteeyes
e102021/06/202021/08/22FranceCommitteeno

 

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

 

dlix_0-1637759341462.png

 

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

1 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

View solution in original post

4 REPLIES 4
lbendlin
Super User
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?

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.

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

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

Helpful resources

Announcements
Microsoft Build 768x460.png

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_carousel_with_text (1).png

Charticulator Design Challenge

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

May UG Leader Call Carousel 768x460.png

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.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

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