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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors