Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |