Forum Discussion
chart with start and end date
I would like to create a line chart based on data that only has a start and end date.
Here is a sample of the data
Type Start Date End Date
| IW | 12/01/2017 | 12/31/2017 |
| IW | 12/01/2017 | 12/31/2017 |
| JL | 12/01/2017 | 12/31/2017 |
| JL | 12/01/2017 | 12/14/2017 |
| EC | 12/15/2017 | 12/31/2017 |
| IW | 12/01/2017 | 12/24/2017 |
Ideally it would group based on Type and then create a line chart with the Y Axis as Type and the X-Axis would be the Start Date to the End Date range. Then for each day in between start and end it would have the Count of Type.
Hi jeffr,
You can try to use below formula to generate the detail date table, then build relationship between original table and new table. After these steps, you can use new date form new table as the axis and drag id to value field with summary mode count.
Original table:
Sample:
Detail = VAR temp = SELECTCOLUMNS ( 'Sample', "Index", [Index], "StartDate", [StartDate], "EndDate", [EndDate] ) VAR vCalendar = CALENDAR ( MINX ( temp, [StartDate] ), MAXX ( temp, [EndDate] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( temp, vCalendar ), [StartDate] <= [Date] && [EndDate] >= [Date] ), "Index", [Index], "StartDate", [StartDate], "EndDate", [EndDate], "Detail", [Date] )Expand tableRelationship
Create line chart with above columns:
Regards,
Xiaoxin Sheng
4 Replies
Hi jeffr,
You can try to use below formula to generate the detail date table, then build relationship between original table and new table. After these steps, you can use new date form new table as the axis and drag id to value field with summary mode count.
Original table:
Sample:
Detail = VAR temp = SELECTCOLUMNS ( 'Sample', "Index", [Index], "StartDate", [StartDate], "EndDate", [EndDate] ) VAR vCalendar = CALENDAR ( MINX ( temp, [StartDate] ), MAXX ( temp, [EndDate] ) ) RETURN SELECTCOLUMNS ( FILTER ( CROSSJOIN ( temp, vCalendar ), [StartDate] <= [Date] && [EndDate] >= [Date] ), "Index", [Index], "StartDate", [StartDate], "EndDate", [EndDate], "Detail", [Date] )Expand tableRelationship
Create line chart with above columns:
Regards,
Xiaoxin Sheng
hey,
if you goal is to show the time period please refer to following posts:
"Event-in-Progress"
https://community.powerbi.com/t5/Desktop/Count-of-Active-Contracts-by-Start-en-End-date/td-p/94104
Hi,
You have reposted this question. I had shared my solution in your previous post. Here is the download link to my solution.
Hope this helps.
