Grouping two different date fields on a common axis

[jay_za]

Hi everyone

Firstly, I'm not certain that the subject will reflect the solution to this problem, but for now it's the direction I'm trying to move in. I'm also fairly certain that I'm missing something very simple and obvious as a solution but I've been staring at this data for so long now that everything is becoming fuzzy.

I have a table of customer support cases that I'm reporting on. It essentially boils down to this structure:

What I need to do is generate a basic clustered column chart of that data showing the count of cases that were opened in each month clustered with the number that were closed. A count of the dates is the easy way, but I'm struggling to come up with a way to create a shared date X axis that both can use. If I use either the open or close date, it throws off the data of the other one's count e.g. case 000001 counts the close date in 2023-12, which is expected. I had the idea of using a lookup table but creating relationships for both fields will create ambiguity. I suspect there may be a way to do with with a summarised table or measures, but I haven't been able to get that right yet.

If anyone has some thoughts on a simple way that I've completely forgotten about, I'd appreciate any help.

[amitchandak]

@jay_za , seem very similar to HR analytics problem, Please find the blog and attached files after signatures

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU

[jay_za]

Thanks very much, @amitchandak. That's a very useful post and I've bookmarked it for future use. Lots of great info in there.

In this particular case, however, I'm looking to group counts of two separate date fields on a common date axis. If I use your data as an example, it would be start and end date. I want to be able to have an X axis showing months (e.g. Jan 2015, Feb 2015, Mar 2015, etc) and the number of joiners and leavers during that month.  So again using your data as the sample:

Jan 2015: 2 started (A, B), 0 ended

Feb 2015: 1 started (C), 0 ended

Mar 2015: 1 started (D), 0 ended

...

Oct 2015: 1 started (J), 1 ended (G)

If I use start date as the X axis, the end dates would be counted along with the start dates e.g. G's end date would be counted as a someone leaving, but it would be counted as leaving in Jun 2015, the start date.

Do you perhaps know of a way around this?