I am fairly sure there is a straightforward answer to this one but I'm afraid I just can't seem to put it all together - and I apologize in advance if this has been answered already but I did have a look and couldn't find an answer!
In summary, I am trying to build a waterfall viz which shows the interaction of a dimension with several categories (i.e. drivers of employee headcount (HC) - new hires, terms, transfers) over time AND showing a start and end 'total' HC count.
My data source is an excel HRIS export which has a line for each active employee (basically a monthly snapshop of the active population since 2017 stitched together) - I refer to this as the "No Change" record for each employee. An active employee who has been at the company since 2017 will have basically 24 lines of data (one for each monthly census 'snapshot'). In addition to this "No Change" record, there is a separate line for each 'coming' and 'going' (e.g. termination, each transfer, each new hire etc) for each month, going back until 2017. So someone who was with the company for a year and then termed would have 13 records - 12 monthly "no change' lines and then 1 'term' line.
I hope to let the user select (assume via a parameter) the start and end dates of the waterfall (e.g. whether they are viewing the HC waterfall for most recently completed month, or select 2+ months, or YTD or quarters etc.), so the waterfall needs to show the starting HC for the starting time period selected, then show the comings and goings (e.g. terms, new hires etc) and ending with the end time period's HC balance.
I looked at the various forum threads and have had a go. I tried the simplest approach first. I built a gannt chart with negative values etc. I then put Census Date (essentially the date of the record for each export of the HRIS system) as a filter and it gives the user the ability to select the time period).
However, the challenge is that if I choose (e.g.) last 3 quarters, it obviously picks up all the employee's records (i.e our example employee above with 13 records will contribute 12 "no change" records and 1 'term' record). The result is sum of 'No change' # which is much higher than the starting headcount. The HC 'No Change' is showing as 43k which is obviously 3x larger than the starting HC figure.
I suspect the answer is in perhaps using an index or window calc but I just can't seem to marry all these techniques together!
Any assistance would be HUGELY appreciated!