Sum values from one table for selected months with slicer per person id with condition

[Vallt]

Hello,

I can not think of a measure that calculates what I want.

To put it simple I have a file with data. It has person id, order types, date of order, hours worked. Each person id has to document all his working hours with a certain order type and hours:

Id         order type        sub-type                                        order indicator      date        hours worked

John      ZWOF        Invoiced labour  (Дейност Труд)              24                 2.5.2025        70

Sam       ZINT           Cleaning  (Почистване)                          02                 3.5.2025        200

John      ZWOF        Invoiced labour (Дейност Труд)               31                 8.6.2025       140

There are other columns, like order sub-types and so on and I succeeded in making the measure sum the desired order types worked hours per person id and skip the values of the rest per person id for a certain time period.

 I use a date slicer on the visual and it works fine. I made a separate Calendar table and linked it to the orders hours table. I made a hierarchy and two slicers – one for year and one for month. I want to see data for whole months in the visual as I set max working hours for whole months and this is fine for me.

The harder thing to do is to define the maximum possible working hours per person id for the selected period in the date slicer. I made a separate table with person ids, months and maximum working hours for each month and set the date slicer to use months, not dates.

Person id     month     max work hrs [Работни дни]

John              1.5.2025        160

Sam              1.5.2025

Jack              1.5.2025

John              1.6 2025        168

Sam              1.6.2025  

Jack              1.6 2025

One specific order type shows the hours that the person was on a holiday, sick, training or anything that prevented him from working. So I made a measure that calculated the maximum possible working hours per month minus the holiday orders hours for person id, which indicates the real maximum working hours for each person id and the selected time period. This works fine.

Now  I see if each person id has documented all his working time – all the documented hours for the period / (maximum possible working hours – holiday hours).

I see his efficiency – only efficient (productive) order types hours / maximum possible working hours for selected period.

I see his efficient order types hours / inefficient (all the hours may be more than the maximum for the period if he worked overtime).

The problem is if I choose a time period of 12 months in the slicer and a certain person id has worked in the company only 6 of them I get the productive orders hours for the selected 12 months, which were generated during only 6 months (which is ok) divided by the maximum working hours for the selected 12 months (which is not ok). This decreases the efficiency of the person id, increasing the maximum working hours for months he did not work in the company. The measure should sum only the max possible hours for the months in which the person id has generated any working hours, not matter the order type and skip the months with no working hours at all for this person id.

For these measures I used :

Productive hrs = CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[OrdType] = "ZISP" || 'ZALL'[OrdType] = "ZWIN" || 'ZALL'[OrdType] = "ZWOF" || 'ZALL'[OrdType] = "ZWIP", 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] <> 55 && 'ZALL'[Индикатор калкулация] <> 56 && 'ZALL'[Индикатор калкулация] <> 02 && 'ZALL'[Индикатор калкулация] <> 60 && 'ZALL'[Индикатор калкулация] <> 51, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0

Max hrs minus productive hrs = ((CALCULATE(SUM('макс раб часове на месец'[Работни дни]), ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0) - (CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] = 55 || 'ZALL'[Индикатор калкулация] = 56, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0)) - (CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[OrdType] = "ZISP" || 'ZALL'[OrdType] = "ZWIN" || 'ZALL'[OrdType] = "ZWOF" || 'ZALL'[OrdType] = "ZWIP", 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] <> 55 && 'ZALL'[Индикатор калкулация] <> 56 && 'ZALL'[Индикатор калкулация] <> 02 && 'ZALL'[Индикатор калкулация] <> 60 && 'ZALL'[Индикатор калкулация] <> 51, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0)

Productivity % = (CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[OrdType] = "ZISP" || 'ZALL'[OrdType] = "ZWIN" || 'ZALL'[OrdType] = "ZWOF" || 'ZALL'[OrdType] = "ZWIP", 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] <> 55 && 'ZALL'[Индикатор калкулация] <> 56 && 'ZALL'[Индикатор калкулация] <> 02 && 'ZALL'[Индикатор калкулация] <> 60 && 'ZALL'[Индикатор калкулация] <> 51, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0) / ((CALCULATE(SUM('макс раб часове на месец'[Работни дни]), ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0) - (CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] = 55 || 'ZALL'[Индикатор калкулация] = 56, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0))

Unproductive hrs = (CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[OrdType] = "ZDAY" || 'ZALL'[OrdType] = "ZCS1" || 'ZALL'[OrdType] = "ZINT" || 'ZALL'[OrdType] = "ZPDI", 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] <> 55 && 'ZALL'[Индикатор калкулация] <> 56, ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0) + ((CALCULATE(SUM('ZALL'[Общо количество]), 'ZALL'[OrdType] = "ZISP" || 'ZALL'[OrdType] = "ZWIN" || 'ZALL'[OrdType] = "ZWIP" || 'ZALL'[OrdType] = "ZWOF", 'ZALL'[Име на разходен ел.] = "Дейност Труд", 'ZALL'[Индикатор калкулация] = 02 || 'ZALL'[Индикатор калкулация] = 60 || 'ZALL'[Индикатор калкулация] = 51)) + 0)

So what I need is that the measure “Max hrs minus productive hrs” checks the months where the person id has no working hours at all and skip them when summing the maximum possible working hours from the other table. This is the calculation:

Max hrs minus productive hrs = ((CALCULATE(SUM('макс раб часове на месец'[Работни дни]), ALLEXCEPT('Technicians branches', 'Technicians branches'[Служител])) + 0) ……

[DataNinja777 · Accepted solution]

Hi @Vallt ,

To ensure that your measure for "Max hrs minus productive hrs" only includes the months where the person actually worked, you need to filter the макс раб часове на месец table to include only those months where the person logged any working hours in the ZALL table. This avoids incorrectly inflating the denominator when the slicer covers months during which the person did not work at all. The following DAX measure will achieve this by cross-filtering the макс раб часове на месец table with only the relevant months per person:

MaxHrs_OnlyForWorkedMonths :=
CALCULATE(
    SUM('макс раб часове на месец'[Работни дни]),
    FILTER(
        'макс раб часове на месец',
        CALCULATE(
            SUM('ZALL'[Общо количество]),
            FILTER(
                'ZALL',
                'ZALL'[Служител] = 'макс раб часове на месец'[Служител] &&
                FORMAT('ZALL'[date], "YYYY-MM") = FORMAT('макс раб часове на месец'[month], "YYYY-MM")
            )
        ) > 0
    )
)

This measure ensures that only the months in which the employee has actually worked are used when summing up their possible maximum hours, aligning the denominator with their actual working activity. You can replace the max hours portion of your current formula with this new measure to make the rest of your productivity calculations more accurate.

Best regards,