Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello everyone,
I'm trying to create a report that calculates Resource Utilization (%). My tables are:
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])
This is the goal of how the visual should look like:
Do this to count workdays:
IsWorkday = SWITCH(WEEKDAY([Date]),1,0,7,0,1)
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingYour 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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHi @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!
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingHow are your relationships set up? Can your measure get the start date through the relationship to the Dates table?
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |