Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
gill_133
Frequent Visitor

Need help for weekly target that is applied to any date range + conditional formatting

Hi there,

 

I'm having trouble getting my head around the steps required for a visualisation relating to staff time. I'm fairly new to this so please bear with me. I haven't come across a forum example that directly relates to the below. 

 

Staff have a daily and weekly minimum contracted hours value (not the same for all staff - some are part-time). My table visualisation shows total staff time and I've got a date slicer that can be used to select any number of previous days/weeks/years. Happy with that. 

 

I'm not great on DAX so the next step I'm not sure of - I'd like to use conditional formatting to show in red where the slicer-selected total hours for any staff exceed EITHER the daily contracted hours, OR the weekly contracted hours. What is the DAX required by a measure that could potentially do this? (is it even possible? if not, just having the weekly exceeded value shown will do)

 

Additional note - staff log hours for various tasks per day; I am looking to sum all time (i.e. from all tasks) over the selected period. 

 

Please see below example of my data for a single staff member and expected result:

Table: Staff time requirements

NameTotal contract hours per weekTotal contract hours per dayDays worked per week
John Bloggs37.57.55

 

Table: Logged time

NameTime (hours)TaskDate
John Bloggs9.5Consulting21/07/2023
John Bloggs7Administration20/07/2023
John Bloggs10Consulting19/07/2023
John Bloggs5Training18/07/2023
John Bloggs3.5Promotion18/07/2023
John Bloggs7.5Consulting17/07/2023
John Bloggs8Administration14/07/2023
John Bloggs12Consulting13/07/2023
John Bloggs1Consulting12/07/2023
John Bloggs7Training12/07/2023
John Bloggs6Promotion11/07/2023
John Bloggs6Consulting11/07/2023
John Bloggs9Consulting10/07/2023

 

Expected result: (based on slicer selection of last 2 calendar weeks)

StaffContract hours for periodSum of time (hours)
John Bloggs7591.5

 

Many thanks for any help!

 

Note - I am a new user and don't have the option to upload example files - hence inclusion as pasted above.

My two example excel files and WIP pbix can be downloaded here: https://www.dropbox.com/scl/fo/vhixn9z55qz8wp8nxj6pr/h?rlkey=gcxvuy1d8hubak1ki6jh8168p&dl=0 

1 ACCEPTED SOLUTION
v-cgao-msft
Community Support
Community Support

Hi @gill_133 ,

 

Please create 3 measures:

Total hours = SUM('Staff time logged'[Time (hours)])
Contract hours for period = 
VAR _min_date = MIN('Staff time logged'[Date])
VAR _max_date = MAX('Staff time logged'[Date])
VAR _weeks = DATEDIFF(_min_date,_max_date,WEEK) + 1
VAR _total = _weeks * MAX('Staff time targets'[Total contract hours per week])
RETURN
_total
Measure = IF([Total hours]>[Contract hours for period],"Red")

Apply the measure to the conditon formatting:

vcgaomsft_0-1690187675529.png

vcgaomsft_1-1690187695051.png

Output:

vcgaomsft_2-1690187727948.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

View solution in original post

6 REPLIES 6
v-cgao-msft
Community Support
Community Support

Hi @gill_133 ,

 

Please create 3 measures:

Total hours = SUM('Staff time logged'[Time (hours)])
Contract hours for period = 
VAR _min_date = MIN('Staff time logged'[Date])
VAR _max_date = MAX('Staff time logged'[Date])
VAR _weeks = DATEDIFF(_min_date,_max_date,WEEK) + 1
VAR _total = _weeks * MAX('Staff time targets'[Total contract hours per week])
RETURN
_total
Measure = IF([Total hours]>[Contract hours for period],"Red")

Apply the measure to the conditon formatting:

vcgaomsft_0-1690187675529.png

vcgaomsft_1-1690187695051.png

Output:

vcgaomsft_2-1690187727948.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

@v-cgao-msft thanks so much, this worked perfectly - just what I needed. 

 

One further query - how would I amend the 'contract hours per period' measure if I had a second staff member with total contract hours per week of 30? (i.e. in that case, the 'max' function would only see the 37.5 value).

 

Kind regards, appreciate any insights. 

foodd
Super User
Super User

Thanks, I've tried to follow the directions in that post as closely as possible in my original post - please let me know how I could improve or if I've done something amiss. 

Please add your work-in-progress Power BI Desktop file, and source data file in Excel format using tables.   Again, this is described in the following post:  How to Get Your Question Answered Quickly  

Got it - I'm a new user and can't attach files, but have updated post above with link to dropbox to download WIP pbix and excel ss files. 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.