Forum Discussion

kangkopi15's avatar
kangkopi15
Helper I
2 years ago
Solved

Utiisation per week

Dear Experts, I need your help again, I want to sum total the utilization per week, except for the activity called "Leave" Activity Utilisation % Start Date Start Date of the week Reso...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi kangkopi15 ，

    You can create a calculated column as below to get it, please find the details in the attachment.

    Utilisation per week = 
CALCULATE(
    SUM('Table'[Utilisation %]),
    FILTER(
        'Table',
        'Table'[Start Date of the week] = EARLIER('Table'[Start Date of the week]) &&
        'Table'[Resource] = EARLIER('Table'[Resource]) &&
        'Table'[End Date of the week] = EARLIER('Table'[End Date of the week])
    )
) / 100

    Best Regards

kangkopi15's avatar
kangkopi15
Helper I
2 years ago

This is the formula on my Excel, I basically want to have a similar formula in the PowerBI data table

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi kangkopi15 ，

    You can create a calculated column as below to get it, please find the details in the attachment.

    Utilisation per week = 
CALCULATE(
    SUM('Table'[Utilisation %]),
    FILTER(
        'Table',
        'Table'[Start Date of the week] = EARLIER('Table'[Start Date of the week]) &&
        'Table'[Resource] = EARLIER('Table'[Resource]) &&
        'Table'[End Date of the week] = EARLIER('Table'[End Date of the week])
    )
) / 100

    Best Regards

    • kangkopi15's avatar
      kangkopi15
      Helper I
      2 years ago

      Hi Anonymous lovely, thank you, that's correct.
      I tried using this syntax, and it gives me the same result as yours

      Weekly Utilisation = CALCULATE(SUM('Table'[Utilisation %]),
      ALLEXCEPT('Table',
      'Table'[Resource], 'Table'[Start Date of the week].[Date], 'Table'[End Date of the week].[Date]
      )
      ) / 100


      However your solution is the most acceptable, so I am going your syntax, thank you