How to depict total working hours for all employees based on date

[Anonymous]

Hello everyone,

I need some ideas on how to solve this, please. I would be so thankful!

I have a table named "FTE by time" (source: Sharepoint), in which the entry date of each employee is listed, with the weekly working hours and all the contract changes regarding the working hours. That means I have numerous "date"-columns. (The more changes in the contracts, the more "date columns" I have)

The table "FTE by time" looks similar to that:

Name   Entry Date   Leaving Date   Weekly Hours   FTE    ChangingStartDate    Weekly Hours   NewFTE   ChangingEndDate

xy               xy               xy                        40                1                  xy                             28              0,8                xy

xy               xy               xy                        35                1                  xy                             20              0,5                xy

ChangingStartDate is the date, in which the weekly hours changes because of parental leave, part-time, etc.

FTE/NewFTE stands for "Full Time Equivalent" - if an employee works 35 or 40 hours, then the FTE equals 1. If he works 25 hours, then it will be calculated proportionally. (It is already calculated in Sharepoint)

The desired result is to create a diagram which sums up the FTE's of all employees by date, there should be no gaps between the dates.
If an employee's contract starts at 1st November 2021, then this employee should also be depicted in the diagram for the 2nd, 3d November, December and all the upcoming months, until there is a change in the weekly working hours.

As you can see here there are gaps. (the 2 different blue colors represent 2 different employees). There shouldn't be any gaps because both employees work every day, of course. Now I need to do that with all the employees in the list, so in the end we can see, how many FTE's did we have on a particular day (summed up)

Best regards

Meri

[Anonymous]

Hi @Anonymous 

I received your reply, but it may have been deleted for some reason. I think your problem is about that you couldn't select columns like "Entry Date", "Leaving Date" or "Weekly Hours" in your dax code.

Here I have some advise.

1. Please check whether you are creating an calcualted table. Maybe you are creating a measure.

2. Please check whether you are caculating in the table which contains columns like "Entry Date", "Leaving Date" or "Weekly Hours".

You can download my sample file in above reply for more details.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous · Accepted solution]

Hi @Anonymous ,

Your table is not suitable for you to show values by multiple condtions, I think you can try to create a calcualted table by dax.

My Sample:

Calculated table:

Combine New Table =
VAR _Date =
    CALENDARAUTO ()
VAR _COMBINE =
    GENERATE ( 'Table', _Date )
VAR _Filter =
    FILTER (
        _COMBINE,
        [Date] >= [Entry Date]
            && IF (
                [Leaving Date] = BLANK (),
                [Date] <= MAXX ( _COMBINE, [Date] ),
                [Date] <= [Leaving Date]
            )
    )
VAR _ADDWEEKHOURS =
    ADDCOLUMNS (
        _Filter,
        "WEEKLY_HOURS",
            IF (
                [Date] < [ChangingStartDate]
                    || [Date] > [ChangingEndDate],
                [Weekly Hours],
                [New Weekly Hours]
            ),
        "Combine_FTE",
            IF (
                [Date] < [ChangingStartDate]
                    || [Date] > [ChangingEndDate],
                [FTE],
                [NewFTE ]
            )
    )
RETURN
    SUMMARIZE ( _ADDWEEKHOURS, [Name], [Date], [WEEKLY_HOURS], [Combine_FTE] )

Dax table looks like as below.

Result:

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[koen2000]

Hi Rico, 

I have a similar situation like this, but a little difference in data. 

Can you assist me with this one? 

I have a table with columns Name | CheckedIn Date Time | CheckedOut Date Time | etc. 

I would like to show the revenue per worked hour by an employee. 

I only have check in date/time & check out date/time. But i think i need a row for every hour the employee has worked?

sample data: 

Name	Checked In Date/Time	Checked Out Date/Time	Statute	Week Nr	Houres Worked	Area	Work Week regime hours	Work Week Regime Minutes
Alfred	7/01/2023 16:00:00	7/01/2023 22:15:00	Flexi	1	6.25	Kitchen	6	15
Alfred	8/01/2023 12:00:00	8/01/2023 15:45:00	Flexi	1	3.75	Kitchen	6	15
Bruce	7/01/2023 11:00:00	7/01/2023 21:00:00	Full Time	1	10	Waiter	37	45
Bruce	8/01/2023 11:00:00	8/01/2023 18:00:00	Full Time	1	7	Waiter	37	45
Bruce	9/01/2023 11:00:00	9/01/2023 21:45:00	Full Time	2	10.75	Waiter	40	00

I hope i'm clear, otherwise feel free to ask! 

Thanks in advance! 

best regards, 

Koen

[amitchandak]

@Anonymous , do you want to consider data between enter date and leaving date ?

if yes, refer

How to divide/distribute values between start date or end date or count days across months/days: https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

[Anonymous]

Hello Amit,

thank you very much for your suggestion. I will need it for sure for further tasks, but unfortunately it didn't solve the problem here. I appreciate your solutions!

Best regards

Meri

[Anonymous]

Hi @Anonymous ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file without sensitive data.

Best Regards,

Rico Zhou

[Anonymous]

It's solved, thank you so much! It was exactly what I was looking for.

Best regards
Meri