Sick leave dashboard

[Rune]

Hi, 

As I am quite new to Power BI and Power query any help is greatly appreciated! 

I have two main question that I want answered for my Sick Leave Dashboard.

1. How many hours (working hours) of sick leave do we have each month?
For this one it is important that I can allocate working hours to the right month based on an entry (keeping in mind that weekends exist).
2. How many people have been sick each month?
For this one it is important that Hans is counted as 1 for every individual month (January to June) and not just for the starting month. I want to be able to have a count for each month throughout the year.


This is what my data set looks like

Person/date of entry	Start Date	End Date	Total amount of working hours
Greta-entrydate	25/03/2019	04/04/2019	60
Hans-entrydate	02/01/2019	27/06/2019	735

Thanks in advance!

[v-kelly-msft · Accepted solution]

Hi @Rune ,

For question 1:

1.Create a calendar table using below dax expression:

Table 2 = CALENDARAUTO()

Then create a column to get the weekday of each day:

column = WEEKDAY('Table 2'[Date],2)

After that,create a measure below:

Sick hours = 
var a= COUNTROWS((FILTER('Table 2','Table 2'[Date]>=CALCULATE(MIN('Table'[Start Date]),ALLEXCEPT('Table','Table'[Person/date of entry]))&&'Table 2'[Date]<=CALCULATE(MAX('Table'[End Date]),ALLEXCEPT('Table','Table'[Person/date of entry]))&&'Table 2'[column]<6)))
Return
a*8-SELECTEDVALUE('Table'[Total amount of working hours])

Finally you will see:

As for question 2,can you provide more details that how to define which month does the sick hours take?For example:Greta has asked for 12hours as sick leave,but how to define whether the 12 hours  happen in March or April?

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[amitchandak]

This measure you can plot with dates to know dates of leave

CALCULATE(countx(SUMMARIZE(filter(CROSSJOIN('Table','DimDate'),'DimDate'[Date] >= 'Table'[AbsenceStartDate] && 'DimDate'[Date]<= 'Table'[AbsenceEndDate]),'Table'[id],'DimDate'[Date]),'DimDate'[Date]))

Or refer to this solution

https://www.dropbox.com/s/1c3eg4y52d49uel/Leave%20test%20data.pbix?dl=0

You can multiply no of Hours with days