Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Calculate average customers seniority

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

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

View solution in original post

4 REPLIES 4
Phil_Seamark
Employee
Employee

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())

qf.jpg

 

Here is the PBIX File

 

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

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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

I need the AVERAGE for all the customers.

Thank you

Michael

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
							) 
						)
					)
					)

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.