cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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:

 OrderId CreatedAt CheckDate InProgress Finished 1 11.01.2021 25.01.2021 X 2 18.01.2021 25.01.2021 3 18.01.2021 25.01.2021 4 25.01.2021 25.01.2021 1 11.01.2021 01.02.2021 X 2 18.01.2021 01.02.2021 X 3 18.01.2021 01.02.2021 4 25.01.2021 01.02.2021 X 5 01.02.2021 01.02.2021 X 6 01.02.2021 01.02.2021 1 11.01.2021 08.02.2021 X 2 18.01.2021 08.02.2021 X 3 18.01.2021 08.02.2021 4 25.01.2021 08.02.2021 X 5 01.02.2021 08.02.2021 X 6 01.02.2021 08.02.2021 X 7 08.02.2021 08.02.2021

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.

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: Id CreatedAt Processed first at CheckDate Days 1 11.01.2021 25.01.2021 14 2 18.01.2021 01.02.2021 14 4 25.01.2021 01.02.2021 7 5 01.02.2021 01.02.2021 0 6 01.02.2021 08.02.2021 7 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: Id CreatedAt last CheckDate Days 1 11.01.2021 08.02.2021 28 2 18.01.2021 08.02.2021 21 4 25.01.2021 08.02.2021 14 5 01.02.2021 08.02.2021 7 6 01.02.2021 08.02.2021 7 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
Solution Sage

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.

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
2 REPLIES 2
Solution Sage

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.

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
Anonymous
Not applicable

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!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

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

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors