Work efficiency calculation

[IvanS]

Hi guys,

I would need your help with calculating work efficiency for our workers. I have 2 tables - FACT_Tasks and DIM_Users

FACT_Tasks contains following fields:
- Task ID

- Working time (in days)

- Worker name

- Close date

DIM_Users contains following fields:
- Worker name

- Employment date

Now, what I need to calculate is YTD work efficiency by dividing cumulative sum of working time by nr. of working days until today with following condition:

- if the person joined before 1.1.2022, then to use as denominator NETWORKDAYS(DATE(YEAR(TODAY()), 1, 1), TODAY(), 1)

- if the person joined later than 1.1.2022, then to use as denominator NETWORKDAYS(Employment date, TODAY(), 1)

I am somehow struggling to create IF condition in measure, so any help is much appreciated!

Thank you
IvanS

[Bifinity_75 · Accepted solution]

Hi @IvanS , If I understood correctly, you have to create a measure similar to this:

Result= SWITCH(
                TRUE(),
                'Your date column' <= DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(DATE(YEAR(TODAY()), 1, 1), TODAY(), 1),"-"),
                'Your date column' > DATE(2022,1,1), DIVIDE(sum('Working time'), NETWORKDAYS(Employment date, TODAY(), 1),"-"),
                "")

Best regards

[IvanS]

Thanks soooo much for help! Worked like a charm! 🙂 

I just got info to calculate the work efficiency on monthly/quarterly basis. Could you please suggest what will be the way how to calculate nr. of working days in specific month/quarter to get the result?

Example:
Worker name - Period (ideally date hierarchy) - Work efficiency

Worker 1 - January - Work efficiency 85%

Worker 1 - February - Work efficiency 70%

Thank you

IvanS