Showing results for 
Search instead for 
Did you mean: 
terrydarnell Frequent Visitor
Frequent Visitor

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

Community Support Team
Community Support Team

Re: Expand roster template

Hi @terrydarnell 


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.




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

Helpful resources

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 185 members 2,774 guests
Please welcome our newest community members: