Calculate average customers seniority

[Anonymous]

Hello friends,

My CustomerStatuses table contains the fields:

CustomerID | StartDate     | EndDate

111111111  | 2017-01-01 | 2017-12-31

222222222  | 2017-03-01 | 2017-12-31

I also have a disconnected Calendar table.

I am looking for a way to create a measure of CustomerSeniority, such as for every date the user sees in the table - the measure will show the average months of customers' seniority.

So in my example, when a user is looking at 2017-04-01 he/she sees: 2 = 3 months for the first user and 1 months for the second = 2 average.

Thank you

Michael

[Phil_Seamark]

HI @Anonymous

I created a simple Date table to be used as the slicer as follows :

Dates = ADDCOLUMNS(
    CALENDARAUTO() ,
    "MonthID" , INT(FORMAT([Date],"YYYYMM")),
    "Month" , FORMAT([Date],"MMM YY")
    )

I set the Month column to be sorted by Month ID - but do not connect this table to your data table!

I can then create the following measure on the data table

Age in Months = IFERROR(
				DATEDIFF(
					MIN('Table1'[StartDate]),
					MIN('Dates'[Month]),
					MONTH
					) +1
				, BLANK())

Here is the PBIX File

https://1drv.ms/u/s!AtDlC2rep7a-kCgm7Wmu8Y13Kxj5

[Anonymous]

Thanks @Phil_Seamark, it works on the customer level, not on total

I need the AVERAGE for all the customers.

Thank you

Michael

[Phil_Seamark]

hi @Anonymous

Without checking on my model, how does this extended version look?

Age in Months = 
IF(ISFILTERED('Table1'[CustomerID]),
 
			IFERROR(
				DATEDIFF(
					MIN('Table1'[StartDate]),
					MIN('Dates'[Month]),
					MONTH
					) +1
				, BLANK()
				),
				CALCULATE(AVERAGEX(
					'Table1',
						DATEDIFF(
							MIN('Table1'[StartDate]),
							MIN('Dates'[Month]),
							MONTH
							) 
						)
					)
					)

[Anonymous · Accepted solution]

Thanks @Phil_Seamark,

Your calculation returns an error when the start date is greater than the Calendar's Date.

But AVERAGEX was definitely the way to do it.

Thanks for the direction!

This is my final result:

Seniority = CALCULATE(AVERAGEX(Subscribers, DATEDIFF(Subscribers[StartDate],MAX('Calendar'[Date]),DAY)+1),FILTER(Subscribers,Subscribers[StartDate]<=MAX('Calendar'[Date])&&Subscribers[EndDate]>=MAX('Calendar'[Date])))

Regards,

Michael