Need a Max and Min Measure for my Report

I have a report that lists the Average time to complete individual tasks by Qtr. Each qtr has x number of tasks completed and the report lists the average time taken to complete the task. I need measures that will also give me the min and max time taken in each qtr.

e.g.
|Raw Data for Qtr 1||

|Task1|8.21|
|Task2|17.71|
|Task3|17.84|
|Task4|23.05|

|Raw Data for Qtr 2||
|Task1|15.21|
|Task2|13|
|Task3|12.14|

|Report|Average| Max |Min|
|Q1 |16.7 |23.05|8.21|
|Q2 |13.45 |15.21|12.14|

Hi @arkhan
​Welcome to eazyBI community!

​I recommend using MIN and MAX functions for your use case.

​You could define new calculated measures with following formulas:

​MIN time:

Min(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember)
    ),
    CASE WHEN
    -- check if issue is relevant for the report
    [Measures].[Issues resolved]  > 0 
    THEN
      [Measures].[Average resolution days]  
    END
)


​MAX time:

Max(
  Filter(
    Descendants([Issue].CurrentMember, [Issue].[Issue]),
    DateInPeriod(
      [Measures].[Issue resolution date],
      [Time].CurrentHierarchyMember)
    ),
    CASE WHEN
    -- check if issue is relevant for the report
    [Measures].[Issues resolved]  > 0 
    THEN
      [Measures].[Average resolution days]  
    END
)


​Best wishes,

​Elita from support@eazybi.com

Thank you very much, this worked.

1 Like