Forum Discussion

Rune's avatar
Rune
Frequent Visitor
6 years ago
Solved

Sick leave dashboard

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 entryStart DateEnd DateTotal amount of working hours
Greta-entrydate25/03/201904/04/201960
Hans-entrydate02/01/201927/06/2019735


Thanks in advance!

Need Help
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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!

2 Replies

  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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!