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.
Dears,
I have a set of Action Items, and we like to visualize the trending of the average age over the last two years.
All items have a start- andand stop-date (if closed), from which I calculate a line-graph for the moving average using the below patern:
Average Age1:=CALCULATE(AVERAGEX(
VALUES ('Calendar'[Date]),
VAR CurrentDate = IF('Calendar'[Date]<MAX('Created Date'[Created Date]),'Calendar'[Date])
VAR CreatedBeforeCurrentDate =
FILTER (
ALL ( 'Source - Action Item Analysis'[CTMS-Trip_Rep_Status.Visit Start Date]),
'Source - Action Item Analysis'[CTMS-Trip_Rep_Status.Visit Start Date] < CurrentDate
)
VAR ResolvedAfterCurrentDate =
FILTER (
ALL ( 'Source - Action Item Analysis'[Completed Date] ),
'Source - Action Item Analysis'[Completed Date] >= CurrentDate || 'Source - Action Item Analysis'[Completed Date] = BLANK()
)
RETURN
CALCULATE (
AVERAGEX(
'Source - Action Item Analysis',
CurrentDate - 'Source - Action Item Analysis'[CTMS-Trip_Rep_Status.Visit Start Date]
),
CreatedBeforeCurrentDate,
ResolvedAfterCurrentDate,
ALL ( 'Calendar' ),USERELATIONSHIP('Source - Action Item Analysis'[CTMS-Trip_Rep_Status.Visit Start Date],'Calendar'[Date])
)))
This works great, though i'd like to provide a better sense of the distribution of Action Item Age by including lines for upper and lower percentiles as well. (due to covid, inactivity on creating new items, or resolving old items is heavily impacted)
However, simply substituting AVERAGEX --> for PERCENTILEX.EXC or MEDIANX informs me that the latter two do not accept String/Bolean or Date as input.
Would anyone know a workaround or alrenate approach to get to a "moving" percentile and or median line in a graph?
Thank you so much in advance!
Kind regards,
Martijn
Solved! Go to Solution.
Have you tried converting the dates into their numeric equivalent and using that instead?
Brilliant! thank you so much, it works perfectly for MEDIANX.
I'm still struggeling with to get PERCENTILEX to work in the above patern, but I will create a new post if needs be.
Thanks again,
Martijn
Have you tried converting the dates into their numeric equivalent and using that instead?
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 |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |