Line graph to show counts of records, by month, based on the record's start and end date

[Coldsmokeski]

Bounced around trying to find a solution and thought I found one using the 'open tickets' solution HERE.  Unfortunately the resulting visual is not bucketing the records by date appropriately.  Hoping for some assistance in finding a solution.

I'm trying to build a visual which shows the count of 'PositionID', by 'OrgGrp', which exists within in a month, utilzing the range between the 'JobStart' and 'JobEnd' to determine whether it should be counted. 

Here's a sample of the dataset:

PositionID	OrgGrp	Org	Job	JobStart	JobEnd
C1-C11-001	C1	C11	CL1	10/1/2019	9/30/2021
C1-C15-002	C1	C15	CL2	10/1/2019	9/30/2021
C2-C21-003	C2	C21	CL3	10/1/2019	9/30/2021
C2-C22-004	C2	C22	CL1	1/15/2020	9/30/2021
C2-C23-005	C2	C23	CL2	1/15/2020	9/30/2023
C2-C24-006	C2	C24	CL3	4/15/2021	9/30/2023
C3-C30-007	C3	C30	CL1	4/15/2021	9/30/2023
C3-C31-008	C3	C31	CL2	4/15/2021	9/30/2023
C3-C31-009	C3	C31	CL3	4/15/2021	9/30/2023
C3-C33-010	C3	C33	CL1	4/15/2021	9/30/2022
C3-C36-011	C3	C36	CL2	4/15/2021	9/30/2022
C4-C41-012	C4	C41	CL3	10/1/2019	9/30/2022
C4-C42-013	C4	C42	CL1	10/1/2019	10/15/2024
C4-C44-014	C4	C44	CL2	10/1/2019	10/15/2024
C4-C44-015	C4	C44	CL3	10/1/2019	10/15/2024
C4-C44-016	C4	C44	CL1	10/1/2022	10/15/2024
C5-C45-017	C5	C45	CL2	10/1/2022	10/15/2024
C5-C45-018	C5	C45	CL3	10/1/2022	10/15/2024

Thanks in advance for any assistance!
 

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[Coldsmokeski]

Thank you for the reply.  However it doesn't quite get me to a solution. 
In the example you provided there is only one date defined, 'Date', for each record in the data table.  The solution I'm after would derive the count for a user defined period by evaluating the 'job start' and 'job end' dates and count that record if it is active during the defined period.
I'm thinking I may have to build a table to break out the records by date based on their start/end dates.  Thoughts?

Any further assistance is very much appreciated.

[Ashish_Mathur]

You are welcome.  I have transformed your data to ensure that only a single date column remains.  Please study the transformation steps in the Query Editor to gain better understanding of what i have done.