Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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
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 ) ) ) )
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
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |