Hi,
I have no ideas by far. Firstly, we correct the relationship as the picture showed. Delete the one in red cross, then the other one can be activated. Secondly, I have several tips here.
1. Line graph has a timeline?
2. Many months have the same TotalWorkHour, so try more times.
Good luck!
Best Regards!
Dale.
the Timeline is 1 visual and the Line Graph is another one. but the TotalWorkHours always has the same value 296032.
My Table 'Date' starts with 01-01-2014 so it probably counts the total number of workdays * 8
i have been changing the relationships but it still doesnt give me the result i wanted.
As you can see we have 70 employees but the formula for TotalWorkingHours reads 280 and with 70 people *8 is 560 hours a day so when i select a month it should read a minimum of 11.200 hours.
Hi,
It seems that you used the wrong column. You should use column "date" from table "Date" and column "Afdeling" from table "Departments". And also other columns. Because table "Verzuimverloop" only has records. It didn't have the whole thing. Have a try please. Good luck!
Best Regards!
Dale
Hi,
The relationships sound good. The problem is that you choose the wrong columns in the report. Please refer to the picture and try again.
Best Regards!
Dale
i changed one of the collums because they refered to the wrong table but im afraid it's still not ok
ive double checked something and when i select just Q2 of 2017 it has 291.984 workinghours and 997 leavehours.
what it does is check who was sick in that period and adds the total amount all the LeaveHours of those people and then counts the total amount of WorkHours of the SAME people in stead of ALL the people
EDIT: ive change the picture because i changed the formula back to what it was
Ive changed the formula but im not sure if it did the trick.
Ive selected one month only in the timeline visual and now it reads 2720 TotalWorkHours
quick calculation indicates that 17 people where sick in that month where normally they would work 17*8*20=2720 hours.
So that part works but that is not what i wanted.
The TotalWorkHours per person still reads 8 hours, no matter what selection i make and it should read 160 TotalWorkHours - (Value TotalLeaveHours) and if i select 2 months it should read 320 (-Value TotalLeaveHours)
If someone has a TotalLeaveHours of 24 then my TotalWorkHours should be 136 (give ive selected 1 month)
however my ultimate goal is that i can have the % of ALL people that were sick per department
ive brainstormed with a collegue of mine concerning this formula and we found out that my way of thinking was wrong.
its hard to explain but ill try none the less
My goal is to calculate the % of sickhours within the total amount of workinghours (100%)
We have been calculating with TotalLeaveHoures and TotalWorkHours but the TotalWorkHours always seem to be wrong.
Where we went wrong is that if i want to calculate the % SickHours we first need the 100% WorkingHours which is 8 hours a day
and untill now we always had the TotalWorkHours calculated as 8 hours - TotalLeaveHours for example 3 TotalLeaveHours means 5 TotalWorkHours but that should have been 8 hours because if we calculate the % TotalLeaveHours we need the 100 % value, in this case 8 hours a day.
When we changed the formulas we found out that if we selected one week it returned 40 TotalWorkHours which is correct but when a person was sick from thursday till wednesday it calculated a TotalLeaveHours of lets say 5 days * 8 hours is 40 hours but in a linegraph those 40 hours where al placed on the first day of that person sickleave which is incorrect.
we needed to find a way to devide thoses 40 hours over the 5 days that he was sick and also make that visual in the linegraph.
My collegue and i brainstormed and came up with the solution to create a new table using this formula:
Verzuimdagen = SUMMARIZE(GENERATE(Verzuimverloop;CALCULATETABLE(VALUES(Date2[Date]);DATESBETWEEN('Date2'[Date];Verzuimverloop[CorrectStartTime];verzuimverloop[CorrectEndTime])));Verzuimverloop[Werknemernummer];Verzuimverloop[Naam voluit];'date2'[Date])
That resulted in a new table, a sort of 'Date' table, but only with dates in it where people where sick.
So if one a give date 1 person was sick that date had 1 line and when 10 people where sick on 1 date it returned 10 lines
the reason we think we need this table is because if someone is sick for 10 days the full 10 days will be shown on the first day of his sickdate instead of devided over the 10 days.
Another example is that if someone called in sick, let say on the 28th of April and he calls in better on the 28th of May the full 20 days will be shown in April instead of 2 days in April and 18 days in May, i hope you understand what im trying to explain here.
We believe that we needed a table where we can display per date if a person was sick so that we can devide the total Sickdays
But now im struggling how i get this data visual in the line graph, any ideas ? (sorry for the long story :))
Hi,
It's a long time from last reply. I find a way to get it finally. It isn't an easy way. Let's make the steps simple.
1. Install R. Microsoft Open R is recommended. Maybe you need help from your IT admin.
2. Check settings.
3. Backup your current PBIX first.
4. In Query Editor, import the source data or copy a current one.
5. Delete other columns to keep three columns. (I don't know if this is right. Just "employee", "startdate", "enddate" needed. You can merge the other columns later if you need them.)
6. Rename "Datum ziektemelding" to "Datumziektemelding" and "Totaal.Naam voluit" to "Totaal.Naamvoluit" (delete the space).
7. Open Run R Script and input these R code.
i <- 1
rows <- nrow(dataset)
while (i <= rows) {
j <- 1
people <- as.character(dataset[i, 1])
start_time <- strptime(dataset[i, 2], "%Y-%m-%dT%H:%M:%S", tz = "UTC")
end_time <- strptime(dataset[i, 3], "%Y-%m-%dT%H:%M:%S", tz = "UTC")
start_date <- strptime(dataset[i, 2], "%Y-%m-%d", tz = "UTC")
end_date <- strptime(dataset[i, 3], "%Y-%m-%d", tz = "UTC")
if (is.na(end_date) == TRUE) {
end_time <- strptime(Sys.time(), "%Y-%m-%d %H:%M:%S", tz = "UTC")
end_date <- strptime(Sys.Date(), "%Y-%m-%d", tz = "UTC")
}
days <- difftime(end_date, start_date, units = "days")
if (is.na(days) == FALSE && days > 0) {
for (j in 1:days ){
if (j == days) {
new_record <- data.frame(Totaal.Naamvoluit = people, Datumziektemelding = as.character(as.Date(start_time) + j), Duedate = as.character(end_time))
}
else {
new_record <- data.frame(Totaal.Naamvoluit = people, Datumziektemelding = as.character(as.Date(start_time) + j), Duedate = as.character(as.Date(start_time) + j))
}
dataset <- rbind(dataset, new_record)
}
}
i <- i + 1
}
result <- dataset
8. If no error message showed up, you can see there are two results. Click the second Table.
9. You can see that the duration is split into days. If you chose a special date duration in the report, the result will be sum of the duration respectively.
10. Change the type of columns to Date type. You can change the names of columns back (add spaces to the names where needed).
11. The formula of CorrectStartTime is still OK. The foumula of CorrectEndTime should be this one:
CorrectEndTime =
VAR et =
TIME ( HOUR ( Verzuimverloop[Duedate] ), MINUTE ( Verzuimverloop[Duedate] ), SECOND ( Verzuimverloop[Duedate] ) )
RETURN
IF (
DATEDIFF (
[Datum ziektemelding],
IF ( ISBLANK ( [Duedate] ), TODAY (), [Duedate] ),
DAY
)
> 0,
[Datum ziektemelding].[date] + TIME ( 16, 30, 0 ),
IF (
et < TIME ( 8, 0, 0 )
|| et > TIME ( 16, 30, 0 ),
[Duedate].[date] + TIME ( 16, 30, 0 ),
[Duedate]
)
)12. Evaluate leave hours:
LeaveHours =
VAR st =
TIME ( HOUR ( Verzuimverloop[CorrectStartTime] ), MINUTE ( Verzuimverloop[CorrectStartTime] ), SECOND ( Verzuimverloop[CorrectStartTime] ) )
VAR et =
TIME ( HOUR ( Verzuimverloop[CorrectEndTime] ), MINUTE ( Verzuimverloop[CorrectEndTime] ), SECOND ( Verzuimverloop[CorrectEndTime] ) )
VAR ml =
TIME ( 12, 0, 0 )
VAR mr =
TIME ( 12, 30, 0 )
RETURN
IF (
WEEKDAY ( [CorrectStartTime], 2 ) IN { 6, 7 },
0,
IF (
st <= ml
&& et <= ml,
24
* ( et - st ),
IF (
st <= ml
&& et >= mr,
24
* ( et - st )
- 0.5,
IF ( st >= mr && et >= mr, 24 * ( et - st ), 9999 )
)
)
) 13. Add a new column Relationship.
Relationship = [CorrectStartTime].[date]
14. Create relationships.
15. Create report.
TotalLeaveHours =
CALCULATE (
SUM ( Verzuimverloop[LeaveHours] ),
FILTER ( Verzuimverloop, Verzuimverloop[LeaveHours] <> 9999 )
)
TotalWorkHours =
SUM ( Dates[IsWorkday] ) * 8
* DISTINCTCOUNT ( Employee[Totaal.Naam voluit] )16. The measure will answer the slicer this time. Please have a try.
You may encounter problem in the part of R. If so, please post snapshot and error message.
Best Regards!
Dale
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
