Forum Discussion
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 al...
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
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