Dynamic Waterfall Chart with Date selections

[elvedinhamidovic]

Hi,

A couple of things I am trying to figure out:

• I am trying to figure out how to change my waterfall chart to be dynamic so when I select a date for MonthYear Start, it will start the chart with that month.  Same thing for MonthYear End, it will end it at that point. 
• My month totals are currently hardcoded and I can't figure out the set analysis to get an active headcount or forecast.
• My new hires, terms, pending and active totals are using set analysis, however, the date ranges are hardcoded in there.  I am not sure how to change so that when new data is entered, it can automatically fill out the chart instead of going in and adding "between date ranges" in the dimension.

Data is attached in a model view format.

[anthonyj · Accepted solution]

Hi,

I  noticed that your Month Start and Month End both come from the same column (CalendarYearMonth) so choosing a date from the Start will also select your end filter.

I've added a set analysis that you can add to your existing measures and set analysis to take the minimum and maximum.

For example

count({$<Date={">=$(=monthstart(min(Date)))<=$(=monthend(max(Date)))"},[Column]={'New Hires'}>}ID])

For this to work however you'll need to select a range in CalendarYearMonth and not just the start and end date.

Regards 

Anthony