Count days between the selected dates of a slicer and a column

[Anonymous]

Hello everyone,

I'm trying to create a report that calculates Resource Utilization (%). My tables are:

• Users
  • Name
  • Start Date
• Date Table 
  • Date
  • isWorkingDay
• Time Entries
  • User
  • Date
  • Hours

The goal is to have a table visual of users with the corresponding Resource Utilization (%) and a Date slicer.

So far I was able to create a measure for the Resource Utilization (%) based on the working days between the Start Date and Today(), but I realized that this is not dynamic, so changing the date filter would do nothing. My thought process to tackle this was to count the number of work days between the resources' Start Date and the max date selected in the date slicer

Any ideas?

Nb. of Work Days = SUM(Users[Working Days])

Work Day Hours = [Nb. of Work Days]*7

Total Submitted (hrs) = sum('Project Approvals'[Submitted(hrs)])

This is the goal of how the visual should look like:

[edhans]

Do this to count workdays:

1. Create a "IsWorkday" column in your date table. One way is to add a column in your Dates table, which will return 0 if it is Sat/Sun, or 1 if Mon-Fri: 

   IsWorkday = SWITCH(WEEKDAY([Date]),1,0,7,0,1)

2. Use the following measure to total the workdays between the dates in your slicer. 

   Count Workdays = 
   VAR FirstDay = CALCULATE(
           MIN('Dates'[Date]),
           ALLSELECTED('Dates'[Date])
       )
   VAR LastDay = CALCULATE(
       MAX('Dates'[Date]),
       ALLSELECTED('Dates'[Date])
   )
   RETURN
   CALCULATE(
       SUM(Dates[IsWorkday]),
       DATESBETWEEN(Dates[Date],FirstDay,LastDay)
   )

You would need more detailed logic in your IsWorkday column if you wanted to exclude holidays and such.

Edit: I just noticed that SQLBI did an article on workday counting last week. A good read. You still need the ALLSELECTED() function above to count days in a slicer range, but you can never go wrong reading their articles.

[Anonymous]

Hi @edhans,

I get the following error when I create the measure you specified:

[edhans]

Your parenthesis are messed up.

You have:

VAR FirstDay = CALCULATE(MIN('Dates'[Date],ALLSELECTED('Dates'[Date])))

I have:

VAR FirstDay = CALCULATE(MIN('Dates'[Date]),ALLSELECTED('Dates'[Date]))

More specifically, I have:

VAR FirstDay = CALCULATE(
        MIN('Dates'[Date]),
        ALLSELECTED('Dates'[Date])
    )

Note the format in the 3rd box is for visual only and DAX doesn't care, but by doing it that way vs on one line, you can easily see where your parens are. You need to move one of the parens at the end to before the comma so MAX is MAX('Dates'[Date]).

I didn't check your entire measure, so fix that, then make sure all other parens are in the right place. The line breaks make it easy to see where specific functions end and others begin, which is hard to see on one row.

[Anonymous]

Hi @edhans ,

Thats a very silly mistake thanks! ( I have to get used to properly formatting my DAX).

This is good progress, but I'm still missing what I originally posted... I need to calculate the working days between the Start Date column in the Users table and the LastDay selected in the date slicer for each User.

My goal is to calculate the total number of working days since the employee's start date (which can then be filtered with a slicer) to then calculate Resource Utilization (%).

Thanks!

[edhans]

Try changing the FirstDate variable to this:

FirstDay = MIN('Employee Table'[Start Date])

Using MIN or MAX here doesn't matter. It will pull the start date for the employee based on the filter context of your visual. You just cannot reference the date directly. MIN/MAX will put it into a single value table instead of being purely a scalar value.

[Anonymous]

@edhans Tried that before and it does not work, view below:

[edhans]

How are your relationships set up? Can your measure get the start date through the relationship to the Dates table?