chart with start and end date

[akshaydz]

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

[Anonymous · Accepted solution]

Hi @akshaydz

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

[v-lili6-msft · Accepted solution]

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

[pmazara]

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

[Anonymous · Accepted solution]

Hi @akshaydz

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