Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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...
@
@Dax
@dax @help
@get help
@NeedHelp
@Please Help
  • PaulOlding's avatar
    PaulOlding
    5 years ago

    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
PaulOlding's avatar
PaulOlding
Solution Sage
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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!