Forum Discussion
Charting a date range
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 |
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.
4 Replies
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/fvgcx8QLqZUHi 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.
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.
