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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
MEverts
Frequent Visitor

Moving Median/Percentile lines on duration (workaround to allow MEDIANX to accept date-values)

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

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

Have you tried converting the dates into their numeric equivalent and using that instead?

View solution in original post

2 REPLIES 2
MEverts
Frequent Visitor

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

 

lbendlin
Super User
Super User

Have you tried converting the dates into their numeric equivalent and using that instead?

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors