Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Values for Specific Dates and Phases

Hi all,   I would like to be able to calculate the average values for a specific employee, but I need the values to be restricted to a certain date range that starts after "Phase 2". See example da...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi Anonymous 

    Create measures

    start date = CALCULATE(MIN(Sheet6[date]),FILTER(ALLEXCEPT(Sheet6,Sheet6[Employee id]),Sheet6[phase]="phase2"))

first 30 days = [start date]+30

flag = IF(MAX(Sheet6[date])<=[first 30 days]&&MAX(Sheet6[date])>=[start date],1,0)

average = CALCULATE(AVERAGE(Sheet6[value]),FILTER(ALLEXCEPT(Sheet6,Sheet6[Employee id]),[flag]=1))

    Best Regards

    Maggie

     

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi Anonymous 

Create measures

start date = CALCULATE(MIN(Sheet6[date]),FILTER(ALLEXCEPT(Sheet6,Sheet6[Employee id]),Sheet6[phase]="phase2"))

first 30 days = [start date]+30

flag = IF(MAX(Sheet6[date])<=[first 30 days]&&MAX(Sheet6[date])>=[start date],1,0)

average = CALCULATE(AVERAGE(Sheet6[value]),FILTER(ALLEXCEPT(Sheet6,Sheet6[Employee id]),[flag]=1))

Best Regards

Maggie

 

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.