chart with start and end date

[jeffr]

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.

[Ashish_Mathur]

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.

[v-shex-msft · Accepted solution]

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

[indranisaha]

Thanks @v-shex-msft. This method works but the only issue I have is the data label in the Y axis duplicates excatly 4 times, not sure why, though the numbers are correct (screenshot attached). Any lead how to resolve this? Thanks!

[WolfBiber]

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