Difficult question, hopefully someone can help me :)

[RvdHeijden]

This is a difficult question and ill try to explain myself as clear as i can (hopefully :))

Im working on a report where management can see the the number of sick people (in a periode and/or department), how long they were sick as wel as a slicer/visual what the % was of people that where sick per department and per period.

That is a big and difficult question and now i'm struggling on how to get this in PowerBi.

Basically i have the following information

- number of total employees (per department)

- a list with all of the sick people (per department), when they were sick (dates) and when they got better (dates)

i want to show this in a visual, for example a Line Chart, where i can see a 'line' in % what the Sick % was in a certain periode and/or department.

For example: i want to see in a Line Chart what the Sick % was in a certain periode/department to see if more people call in sick over time.  

[v-shex-msft]

Hi @RvdHeijden,

You can try to use below formula if it suitable for your requirement:

Sample measure: current sick persons(per department) percent of total department person.

Running total Percent = 
var currDepart=LASTNONBLANK('Sick Records'[Department],[Department])
var temp=FILTER(ALL('Sick Records'),[Got Well Date]>=MAX([Sick Date])&&[Department]=currDepart)
return
COUNTAX(DISTINCT(SELECTCOLUMNS(temp,"Person",[Person])),[Person]) /LOOKUPVALUE(Department[Count],Department[Department],currDepart)

Table structure:

If above is not help, can you please share some sample data to test?

Regards,

Xiaoxin Sheng

[RvdHeijden]

i had to change the formula a bit but it works as a charm now, however the result wasn't what i expected seeing that a department of 2 people has a 50% sickrate in a certain month.

But that person was sick for 2 days and not the whole month.

I need to incorporate the number of hours that person worked that month.

If i have a department of 5 people that all work 40 hours a week and 1 of those 5 people is sick for 3 days (=24 hours).

The formula should read 5*40=200 hours (=100%) and24/200=12 % Sickrate

[RvdHeijden]

@v-shex-msft i got a mail saying that you closed this thread ??

My problem hasn't been solved yet

[v-shex-msft]

Hi @RvdHeijden,

I thought you have solve the issue by yourself, but it seems not. Did you have any other problems cannot solved?

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft i sure do 🙂

I stated the problem 2 posts earlier.

The formula now checks the number of people per department and, for example, if i have a department of 4 people and 2 people where sick that month the formula states that there was a sick rate of 50% en that is not what i wanted.

i have to incorporate the number of working hours per person per department and then check the number of (working)hours that a person is sick.

So if that department has 4 employees that all work 8 hours a day, 40 per week that means that month i have (based on 21 working days) 21*8*4=672 working ours a month for that department.

If 1 person is sick for 2 days (16 hours) that means i have a sick rate of (16/672) 2,4 % and not the 25% the formula returns now

[v-shex-msft]

Hi @RvdHeijden,

About calculate with work hour, you can add a calculated column to calculate the sick leave hours of current person, then get the percent of total sick leave hours of total work hours.

Current Sick Hour = 
var currMin=MAX('Sick Records'[Sick Date])
var currMax=MAX('Sick Records'[Got Well Date])
var currDate=MAX(DateTable[Date])
var leftPart=DATEDIFF(currMin,DATE(YEAR(currMin),MONTH(currMin),DAY(currMin))+TIME(18,0,0),HOUR)-if(HOUR(currMin)<11,2,if(HOUR(currMin)>11&&HOUR(currMin)<13,13-HOUR(currMin)))
var rightPart=DATEDIFF(DATE(YEAR(currMax),MONTH(currMax),DAY(currMax))+TIME(8,0,0),currMax,HOUR)-if(HOUR(currMin)>13,2,if(HOUR(currMin)>11&&HOUR(currMin)<13,13-HOUR(currMin)))
return
if(OR(WEEKDAY(currDate,1)=1,WEEKDAY(currDate,1)=7)||
	OR(DATEVALUE(currDate)<DATEVALUE(currMin),DATEVALUE(currDate)>DATEVALUE(currMax)),0,
	if(DATEVALUE(currDate)>DATEVALUE(currMin)&&DATEVALUE(currDate)<DATEVALUE(currMax),8,
		if(DATEVALUE(currDate)=DATEVALUE(currMin),leftPart,rightPart)))

Total Sick Hours (except holiday) = 
var currMin=MAX('Sick Records'[Sick Date])
var currMax=MAX('Sick Records'[Got Well Date])
var leftPart=DATEDIFF(currMin,DATE(YEAR(currMin),MONTH(currMin),DAY(currMin))+TIME(18,0,0),HOUR)-if(HOUR(currMin)<11,2,if(HOUR(currMin)>11&&HOUR(currMin)<13,13-HOUR(currMin)))
var rightPart=DATEDIFF(DATE(YEAR(currMax),MONTH(currMax),DAY(currMax))+TIME(8,0,0),currMax,HOUR)-if(HOUR(currMin)>13,2,if(HOUR(currMin)>11&&HOUR(currMin)<13,13-HOUR(currMin)))
var daydiff=if(DATEDIFF(currMin,currMax,DAY)<2,0,DATEDIFF(currMin,currMax,DAY)-2)
var weekendCount=COUNTX(FILTER(CALENDAR(currMin,currMax),WEEKDAY([Date],1)=1||WEEKDAY([Date],1)=7),[Date])+0
return
leftPart+rightPart+(daydiff-weekendCount)*8

Notice: In my calculation, work hour 8:00 to 18:00 ,break time 11:00 to 13:00, you also need to use the date column from calendar table as the axis.

If above not help, please share the detail sample data to test.

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft

First of all..... wow 🙂 .... thanks for the help so far.

Im struggling (big time) with your formulas, not with the formula itself but more where (in which table) to put it.

if i want to calculate the Sick rate % per department i first need to get the 100% of total working hourst for that department right ?

For example: 10 people, (all fulltime) so that means total work hourst is 10 (people) * 8 (workinghours p/p) *5 (workingdays) = 400 workinghourst.

But this data isn't incorporated in your formula is it ? the number of people per department ?

[v-shex-msft]

Hi @RvdHeijden,

My formula only calculate sick hour of current row(records table) with current date(from calendar table).

You need to summary these records with the same department and "divide" them with today's department total work hours.(department person count * 8/0)

Formula logic:

Check current date with sick start/end date.

a. If it equal the start date, calculate the left part(today remaining work hours)

b. If it equal to end date, calculate the right part(today started work hours)

c. If it large than start date and smaller than end date and is the workday, return 8.(date part not the same date of start date and end date)

d. Saturday and Sunday return 0.

BTW, total sick hour seems useless in your scenario.

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft

ive calculated the number of employes * 8 / 0 but then the result is the 'infinity' symbol

Aantal werkuren per afdeling = departments[Aantal Medewerkers In Dienst]*8/0

WHy do i need to devide it by 0 ? if i remove that part i get the workinghours

Another EDIT 🙂 :
I used your formula but it returns and error on the DATEDIFF function, the start date cannot be greater than the enddate.

 

[v-shex-msft]

Hi @RvdHeijden,

The 8/0 means the work hour 8 hours or 0 hour.(workday 8, break day 0 )

>>I used your formula but it returns and error on the DATEDIFF function, the start date cannot be greater than the enddate.

You can add some conditions to check the value range and replace them with a default value.(My formula doesn't test on real data, it just a sample )

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft

The formula on the working hours return an 'infinity' symbol, any ideas why ?

Furthermore the error given on the DATEDIFF formula, i'm not sure how to correct that. i know this could happen when i have BLANK values, could this be the case ?

If so can we change the formula so that is can handle blank values ?

[v-shex-msft]

Hi @RvdHeijden,

"* 8/0" means "multiply 8" or "multiply 0", the number is based on work day.(work day 8 hour, break day 0 hour)

>>Furthermore the error given on the DATEDIFF formula, i'm not sure how to correct that. i know this could happen when i have BLANK values, could this be the case ?

For this part, I often check the value before used in datediff.(if the second parameter less than first, use first one replace the second parameter, DATEDIFF(first parameter,first parameter,hour) =0 )

Regards,

Xiaoxin Sheng

[RvdHeijden]

So i can keep this formula and forget the /0 right ?

Aantal werkuren per afdeling = departments[Aantal Medewerkers In Dienst]*8

The error on the DATEDIFF formula, how do i correct is so it can coop with blanks ?

Current Sick Hour =
var currMin=MAX(Verzuimverloop[Datum ziektemelding])
var currMax=MAX(Verzuimverloop[DueDate])
var currDate=MAX('Date'[Date])
var leftPart=DATEDIFF(currMin;DATE(YEAR(currMin);MONTH(currMin);DAY(currMin))+TIME(16;30;0);HOUR)-if(HOUR(currMin)11;2;if(HOUR(currMin)>11&&HOUR(currMin)<13;13-HOUR(currMin)))
var rightPart=DATEDIFF(DATE(YEAR(currMax);MONTH(currMax);DAY(currMax))+TIME(8;0;0);currMax;HOUR)-if(HOUR(currMin)13;2;if(HOUR(currMin)>11&&HOUR(currMin)<13;13-HOUR(currMin)))
return
if(OR(WEEKDAY(currDate;2)=6;WEEKDAY(currDate;2)=7)||
 OR(DATEVALUE(currDate)<DATEVALUE(currMin);DATEVALUE(currDate)>DATEVALUE(currMax));0;
 if(DATEVALUE(currDate)>DATEVALUE(currMin)&&DATEVALUE(currDate)<DATEVALUE(currMax);8;
  if(DATEVALUE(currDate)=DATEVALUE(currMin);leftPart;rightPart)))

[RvdHeijden]

@v-shex-msft any ideas ?

I hope you or someone else can help me out in this  one

[RvdHeijden]

@v-shex-msft No idea ?

how do i use your idea:

For this part, I often check the value before used in datediff.(if the second parameter less than first, use first one replace the second parameter, DATEDIFF(first parameter,first parameter,hour) =0 )

And use this in my formula so that it can deal with BLANK values

Current Sick Hour =
var currMin=MAX(Verzuimverloop[Datum ziektemelding])
var currMax=MAX(Verzuimverloop[DueDate])
var currDate=MAX('Date'[Date])
var leftPart=DATEDIFF(currMin;DATE(YEAR(currMin);MONTH(currMin);DAY(currMin))+TIME(16;30;0);HOUR)-if(HOUR(currMin)<11;2;if(HOUR(currMin)>11&&HOUR(currMin)<13;13-HOUR(currMin)))
var rightPart=DATEDIFF(DATE(YEAR(currMax);MONTH(currMax);DAY(currMax))+TIME(8;0;0);currMax;HOUR)-if(HOUR(currMin)>13;2;if(HOUR(currMin)>11&&HOUR(currMin)<13;13-HOUR(currMin)))
return
if(OR(WEEKDAY(currDate;2)=6;WEEKDAY(currDate;2)=7)||
 OR(DATEVALUE(currDate)<DATEVALUE(currMin);DATEVALUE(currDate)>DATEVALUE(currMax));0;
 if(DATEVALUE(currDate)>DATEVALUE(currMin)&&DATEVALUE(currDate)<DATEVALUE(currMax);8;
  if(DATEVALUE(currDate)=DATEVALUE(currMin);leftPart;rightPart)))

[v-shex-msft]

Hi @RvdHeijden,

My formula seems bring you some confused.
Actually you can calculate with simple requirement, then modify it with additional conditions:
1. Calculate the date diff.(new condition: break days not need to calculated)
2. Consider the break days, add conditions to filter the break days.
3. Convert to work hours.(new  condition: break hours should be filtered)
4. Add conditions to filter the break hours.
5. Summary these values with the same customer.
6. Add conditions to summary specify date range values.
7. Modify it to dynamic date range calculate based on based on date table.
8. Summary same department work hours.

Detailed description of formula calculate:

Sick leave range: 5/2/2017 10:00 am to 5/8/2017 11:00 am

Left part: 5/2/2017 10:00 am to 5/2/2017 6:00 pm (6 hours)
Middle part: 5/3/2017 to  5/5/2017 (3 days = 24 hours)
Right part: 5/8/2017 8:00 am to 5/8/2017 11:00 am (3 hours)

Total: 6+24+3=33 hours

Regards,

Xiaoxin Sheng

[RvdHeijden]

@v-shex-msft

First of all i want to thank your for your time an advise but im afraid im to much of a newb when it comes to DAX to translate your tips into changing the formula.

Can you alter the formula so that it works ?

Your advice seems to be good and should give me the result i want (at least i think it does) but can you change my formula ?

[RvdHeijden]

@v-shex-msft

im sorry to keep having to 'stalk' you 🙂 🙂 but i think that if you can alter my formula like you said this problem will be solved.

I would really appreciate it if you would help me with this formula

Furthermore its the last item on this rapport so i really would like to finish it

[RvdHeijden]

@v-shex-msft

Your visual is exactly the thing i want to see and i almost have your formula working.

However when i saw the results in the Line Chart i knew that the result was wrong

Our 2 biggest departments have a sick rate of 20/25% that is not true so i think their is a fault in de formula (probably my mistake somehow/somwhere)

The Dark line has 30,5% sickrate in Januari 2016 that department has 71 employees and that month 8 times someone called in sick. That means the sickrate should be 8/71=0,11 so 11% and not the 30,5% it shows in the visual

This is the formula ive got so far

Running total Percent =
var currDepart=LASTNONBLANK(Verzuimverloop[Afdeling];[Afdeling])
var temp=FILTER(ALL(Verzuimverloop);[DueDate]>=MAX([Datum ziektemelding])&&[Afdeling]=currDepart)
return
COUNTAX(DISTINCT(SELECTCOLUMNS(temp;"Naam voluit";[Naam voluit]));[Naam voluit]) /LOOKUPVALUE(departments[Afdeling];departments[Afdeling];currDepart)