Calculate Overtime

[Vitaliy]

Hi all!

I have a data model ( you can download pbix file here)

i want to build a report with table like this one below:

But my Overtime field and Overtime card KPI in total values is not fully correct, they are Blank.
How I calculate:

LogHours_forPeriod = 
CALCULATE(
    [LogHours];
    USERELATIONSHIP(worklogs[date_only];DateTime_Vacations[Date])
)

PlannedHours_forMonth = SUMx(VALUES(worklogs[username]);CALCULATE(sum(planned_hours[Working hours])))

Overtime help measure = worklogs[LogHours_forPeriod]-planned_hours[PlannedHours_forMonth]

And Overtime:

Overtime = SUMX(FILTER(SUMMARIZE('worklogs';worklogs[username];"ABCD";[Overtime help measure]);[Overtime help measure]>0);[Overtime help measure])

but I want to show in Total row of the Overtime field value = 32 (sum of positive values for every month and user), now this cell is blank (Overtime KPI card Blank too)
What's wrong in my "Overtime" measure?
Please, help!

Thanks.

[v-shex-msft]

Hi @Vitaliy ,

I can't view your sample file, it seems like you haven't shared it correctly.

Regards,

Xiaoxin Sheng

[Vitaliy]

@v-shex-msft  sorry, link was updated

[v-shex-msft · Accepted solution]

Hi @Vitaliy ,

After play with your sample, I found your overtime measure contians filter on 'overtime help' measure if its result less than zero. 

Overtime = SUMX(FILTER(SUMMARIZE('worklogs',worklogs[username],"ABCD",[Overtime help measure]),[Overtime help measure]>0),[Overtime help measure])

Regards,

Xiaoxin Sheng