Charting a date range

[Anonymous]

Below is an example of data I have.  I have a list of assignments, when they begin and when they end.  As you can see some assignment dates overlap each other meaning they are active during the same time and some do not overlap.  I would like to create a chart where the x-axis shows a calendar month and for each month it counts how many of assignments start and end date overlapped with the calendar month. E.g. looking at the data below for January 2023, I should see a count of 1, February 2023 a count of 2, March 2023 a count of 1, April a count of 2, etc. I would like the calendar to continue to generate new dates as time goes on without having to adjust, so that when a new year begins to just keeps going.  Please assist.

Assignment #	Start Date	End Date
555	01/04/2023	02/23/2023
444	02/15/2023	05/01/2023
333	04/20/2023	06/12/2023
222	06/13/2023	07/04/2023

[Anonymous · Accepted solution]

Hi @Anonymous ,

Thanks @amitchandak  for the quick reply and solution. Here is my alternative approach for your reference:

1.Click "transform data" to enter the power query and add a custom column.

List.Dates([Start Date],Duration.Days([End Date]-[Start Date])+1,#duration(1,0,0,0))

2.Expand to New Rows.

3.Changes the data type to a date type.->Close and Apply.

4.We can create a date table.

Date = CALENDAR(DATE(2023,1,1),DATE(2024,12,31))

5.We can create a measure.

Measure = 
var _table=ADDCOLUMNS(ALL('Table'),"Year",YEAR([Custom Date]),"Month",MONTH([Custom Date]))
var _table2=SUMMARIZE(_table,[Assignment #],[Year],[Month])
RETURN 
COUNTROWS(FILTER(_table2,[Month]=MAX('Date'[Date].[MonthNo]) && [Year] =MAX('Date'[Date].[Year])))

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[Anonymous]

Hello,

Thank you both for your responses. 

When I tried the first approach I am getting this error

When I tried the second approach, after step 3 of close and apply I am getting this message and cannot move forward.  Please assist.

Thank you!

[Anonymous]

Hi @Anonymous ,

Please disconnect the model relationship in Power BI Desktop then go into power query to process the data then close and apply then connect your model relationship again.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[amitchandak]

@Anonymous ,With a date table having active join with Start date and inactive join with end date

Started = CALCULATE(COUNT(Assignment[Assignment #]),USERELATIONSHIP(Assignment[Start Date],'Date'[Date]) )

Ended = CALCULATE(COUNT(Assignment[Assignment #]),USERELATIONSHIP(Assignment[End Date],'Date'[Date]),not(ISBLANK(Assignment[End Date])))

Active Assignment = CALCULATE(COUNTx(FILTER(Assignment,Assignment[Start Date]<=max('Date'[Date]) && (ISBLANK(Assignment[End Date]) || Assignment[End Date]>max('Date'[Date]))),(Assignment[Assignment #])),CROSSFILTER(Assignment[Start Date],'Date'[Date],None))

Seem like very similar to HR Analytic Solution

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970
Power BI HR Active Employee Tenure Bucketing, and Hired, Terminated, and Active employees: https://youtu.be/fvgcx8QLqZU