cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JMPowerBI
Helper I
Helper I

Measure for average processing time with data slicers

Hello everybody!

 

I have the following scenario: I have a table with order IDs. This table also contains the creation date of the orders and the current check date (every seven days). There are also two columns for orders in process and finished orders.

 

That means on every check date (every seven days) a new row is created for each order, where the current status of the order (In process or finished) is newly evaluated and marked in the corresponding column.

 

This is the table with example data:

 

OrderIdCreatedAtCheckDateInProgressFinished
111.01.202125.01.2021X 
218.01.202125.01.2021  
318.01.202125.01.2021  
425.01.202125.01.2021  
111.01.202101.02.2021 X
218.01.202101.02.2021X 
318.01.202101.02.2021  
425.01.202101.02.2021X 
501.02.202101.02.2021X 
601.02.202101.02.2021  
111.01.202108.02.2021 X
218.01.202108.02.2021 X
318.01.202108.02.2021  
425.01.202108.02.2021X 
501.02.202108.02.2021 X
601.02.202108.02.2021X 
708.02.202108.02.2021  

 

JMPowerBI_0-1621245726450.png

 

Now I want to create a measure that always calculates the average processing time for a selected check date.

This is my current calculation for it:

 
Average Processing Time (in days) =

var _processingTime =
CALCULATE(
((COUNTROWS(Data)*FIRSTDATE(Data[CheckDate])) - SUM(Data[CreatedAt]))/COUNTROWS(Data),
FILTER(KEEPFILTERS(Data), Data[CheckDate]=SELECTEDVALUE(Data[CheckDate])),
FILTER(KEEPFILTERS(Data), Data[InProgress]<>BLANK() ||Data[Finished]<>BLANK())
)
return _processingTime
 

The problem is that my Measure does not use the check date when an order was first given the status "In Progress" or "Finished". Instead, it always uses the check date selected by the filter.

For example, in the screenshot the measure shows a result of 15.40 days. 

 
JMPowerBI_0-1621244867649.png

 

But the correct solution should be 8.4 days as is shown here, which I need for my report: 

 

Correct Average Processing Time for CheckDate=08.02.2021:   
    
IdCreatedAtProcessed first at CheckDateDays
111.01.202125.01.202114
218.01.202101.02.202114
425.01.202101.02.20217
501.02.202101.02.20210
601.02.202108.02.20217
    
Calculation (14 + 14 + 7 + 0 + 7) / 5 = 8,4   

 

And this is the current wrong calculation of my measure: 

 

Current wrong calculation of Average Processing Time for CheckDate=08.02.2021:   
    
IdCreatedAtlast CheckDateDays
111.01.202108.02.202128
218.01.202108.02.202121
425.01.202108.02.202114
501.02.202108.02.20217
601.02.202108.02.20217
    
Calculation (28 + 21 + 14 + 7 + 7) / 5 = 15,4   

 

Does anybody have an idea how to rewrite this measure to get the correct average processing time, even when I filter my data with the data slicers as shown in the screenshot? Hope you guys can help me out!

 

Thank you and kind regards!

 

JMPowerBI

1 ACCEPTED SOLUTION
PaulOlding
Solution Supplier
Solution Supplier

This measure returns the 8.4 you're expecting for 8 Feb selection.

 

It iterates the unique combinations of Order ID and CreatedAt, calculates the first date when In Progress or Finished (called _FirstProcessed) & the number of days between that and CreatedAt (_CheckDays) for each row and finally gives the average.

PaulOlding_1-1621249712087.png

 

 

Avg Processing Time =
VAR _SlicerDate = SELECTEDVALUE(Data[CheckDate])
VAR _Result =
AVERAGEX(
SUMMARIZE(Data, Data[OrderId], Data[CreatedAt]),
VAR _FirstProcessed =
CALCULATE(
MIN(Data[CheckDate]),
Data[CheckDate] <= _SlicerDate,
Data[InProgress] = "X" || Data[Finished] = "X"
)
VAR _CheckDays =
IF(NOT ISBLANK(_FirstProcessed),
INT(_FirstProcessed - Data[CreatedAt]),
BLANK()
)
RETURN _CheckDays
)
RETURN
_Result

View solution in original post

2 REPLIES 2
PaulOlding
Solution Supplier
Solution Supplier

This measure returns the 8.4 you're expecting for 8 Feb selection.

 

It iterates the unique combinations of Order ID and CreatedAt, calculates the first date when In Progress or Finished (called _FirstProcessed) & the number of days between that and CreatedAt (_CheckDays) for each row and finally gives the average.

PaulOlding_1-1621249712087.png

 

 

Avg Processing Time =
VAR _SlicerDate = SELECTEDVALUE(Data[CheckDate])
VAR _Result =
AVERAGEX(
SUMMARIZE(Data, Data[OrderId], Data[CreatedAt]),
VAR _FirstProcessed =
CALCULATE(
MIN(Data[CheckDate]),
Data[CheckDate] <= _SlicerDate,
Data[InProgress] = "X" || Data[Finished] = "X"
)
VAR _CheckDays =
IF(NOT ISBLANK(_FirstProcessed),
INT(_FirstProcessed - Data[CreatedAt]),
BLANK()
)
RETURN _CheckDays
)
RETURN
_Result

View solution in original post

Hello @PaulOlding!

 

Many, many thanks for your wonderful and super fast solution! It works perfectly, I am so happy!

Thank you very much and many greetings!

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.