cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## chart with start and end date

Hi ,

Can any body help to plot a cohort analysis for the below table

Am trying to generate a detail table which will have the all the months for each customer from start to end. The detail table could be used to generate the cohort chart

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resident Rockstar

## Re: chart with start and end date

Note : You can combine the below steps as well.

1. Create a Calandar table with this formula. You can modify the date range as you need them.

`Calendar Date = CALENDAR("2011-01-01","2019-12-31")`

2. Use this formula in Modelling -> New Table

```Ex_Generate =
SELECTCOLUMNS(
FILTER(
GENERATE('Table1','Calendar Date'),
'Calendar Date'[Date]>= 'Table1'[Start Date] && 'Calendar Date'[Date]<'Table1'[End Date]
),
"CustID",Table1[CustomeID],
"StDate",Table1[Start Date],
"EndDate",Table1[End Date],
"Date",'Calendar Date'[Date],
"YearMonth1",CONCATENATE(YEAR('Calendar Date'[Date]),FORMAT(MONTH('Calendar Date'[Date]),"00"))
)```

3. If you pull custmer id, and YearMonth column in your visual you will see the required data.

Thanks

Raj

Highlighted
Community Support

## Re: chart with start and end date

Hi,@akshaydz

Maybe you also can try another method like below

Step 1:

Create a CALENDAR table:

`Date = CALENDARAUTO()`

Step 2:

Crossjoin those two tables:

```Table =
FILTER (
CROSSJOIN ( Table2, 'Date' ),
'Date'[Date] >= Table2[start_date]
&& 'Date'[Date] <= Table2[end_date]
)```

Step 3:add a column by months:

`Column = FORMAT ( 'Table'[Date], "mm" ) & " " & YEAR ( 'Table'[Date] )`

NEW TABLE as below:

drag  fields into visual and result:

Here is demo ,please try it .

https://www.dropbox.com/s/gxa26tlz05t3kjh/chart%20with%20start%20and%20end%20date.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
Highlighted
Resident Rockstar

## Re: chart with start and end date

Note : You can combine the below steps as well.

1. Create a Calandar table with this formula. You can modify the date range as you need them.

`Calendar Date = CALENDAR("2011-01-01","2019-12-31")`

2. Use this formula in Modelling -> New Table

```Ex_Generate =
SELECTCOLUMNS(
FILTER(
GENERATE('Table1','Calendar Date'),
'Calendar Date'[Date]>= 'Table1'[Start Date] && 'Calendar Date'[Date]<'Table1'[End Date]
),
"CustID",Table1[CustomeID],
"StDate",Table1[Start Date],
"EndDate",Table1[End Date],
"Date",'Calendar Date'[Date],
"YearMonth1",CONCATENATE(YEAR('Calendar Date'[Date]),FORMAT(MONTH('Calendar Date'[Date]),"00"))
)```

3. If you pull custmer id, and YearMonth column in your visual you will see the required data.

Thanks

Raj

Highlighted
Community Support

## Re: chart with start and end date

Hi,@akshaydz

Maybe you also can try another method like below

Step 1:

Create a CALENDAR table:

`Date = CALENDARAUTO()`

Step 2:

Crossjoin those two tables:

```Table =
FILTER (
CROSSJOIN ( Table2, 'Date' ),
'Date'[Date] >= Table2[start_date]
&& 'Date'[Date] <= Table2[end_date]
)```

Step 3:add a column by months:

`Column = FORMAT ( 'Table'[Date], "mm" ) & " " & YEAR ( 'Table'[Date] )`

NEW TABLE as below:

drag  fields into visual and result:

Here is demo ,please try it .

https://www.dropbox.com/s/gxa26tlz05t3kjh/chart%20with%20start%20and%20end%20date.pbix?dl=0

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Frequent Visitor

## Re: chart with start and end date

Mate, I have been looking for this for a long time and your solution is eloquent. Brilliant! Thank you

Announcements

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

#### Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

#### Get Ready for Power BI Dev Camp

We are thrilled to announce we will begin running a monthly webinar series named Power BI Dev Camp.

Top Solution Authors
Top Kudoed Authors