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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Expand roster template

Hi all,

 

Completely stuck with part of a report build where we are showing the number of hours delivered against a planned schedule, the part we are stuck on is to calculate for each day what the requirement should be using the data we have.

 

The base rosters are shown in a format like:

 

Location id |Shift grade |Start date |End date |hours per shift | Mon | Tues | Wed | Thurs | Fri | Sat | Sun

 

The location ID - will be a unique identifyer for the site location.

 

Shift grade - We need to be able to seperate the requirement by grade, for example this might be "security officer", or "store detective" in our data.

 

Start date - the date the roster pattern comes into effect

End date - the date the roster pattern ends

hours per shift - for each shift in the pattern, total hours

 

The under each weekday name, it will display a true false stating if this particular shift is included in this day.

 

Each location, will have multiple lines like this, so for example if the site has a 9-5 shift Monday to Friday, they will have one line for this, then if they have another shift pattern that is 12-9 Wednesday to Saturday, there will be line for this, and of course there might be different lines for the shift grades also.

 

This requirement alone was making it difficult to do, however the added complexity is, that if there are two lines for the same grade, but where days and hours might vary, but the start date is the following week from the other line (there might be up to 4 lines each starting a week apart), then this becomes a rolling pattern, with the earliest date being the first week, the next week being week 2 etc. locations may have 1 week, 2 week, 3 week or 4 week patterns, and in the current data set, the only way for me to see this is if there are different lines, same grade but differing start dates.

 

I appreciate the data set is not ideal, but I thought I would reach out for advice before collating the information in a different format, as this would require collating thousands of responses from hundreds of sources, and albeit not ideally formatted, I know the data I have to be accurate.

 

I hope the above description makes sense, but please let me know. If any further use, below is a screenshot of one location that has multiple weeks, there is only one grade showing, but there could be different grades also, but the pattern for these might be different, so maybe I will bring in different queries for different grades if this makes it even further complicated.

 

shift template example.JPG

1 REPLY 1
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

Sorry I can’t figure out your question indicated above, could you please upload your files to One Drive and share us the link? It’s preferred that show us your requirements/goals you wanna achieve using graph.

 

Please make sure you’ve masked the confidential info before sharing.

 

Thanks!

 

Best regards,

Dina Ye

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.