Dividing and summing records with total hours for a time span into hours per week / month

[LtKvasir]

Hi,

I need some help building a resourcen utilization dashboard based on our D365 CRM. I am already struggeling with the basic data model. Any help and tips are appreciated, that give me a starting point how to proceed.

I want to reproduce a view that is similar to what D365 PSA yields per project. This is a table with resources in rows, and time frames (can be changed from week to month). In the cell the SUM of planned hours / effort per ressource in that time frame is shown. Something like this (red encircled numbers matter for me - the rest is not important):

The data i have available to build that analysis is a data-table which has a single line per resource/task combination. Including a start date, end date and a total of hours for that task: 

How can i build such a visualization? Should I transform my data - and how?

My biggest issue is to convert that total hour number into a 'hours per week' format and then show the sum for a month ...

Thanks.

[LtKvasir]

Hi @Anonymous and thanks for your reply. Sorry for not being very clear. 

I find it difficult to explain the problem. 

I do have data from D365 PSO that is in a model like shown below. Each row has a resource and a task name, a start and end date as well as a field for hours that the task needs.

Task	Resource	Start	End	Hours
A	Martin	01.10.2022	01.11.2022	10
B	Martin	01.10.2022	01.01.2023	60
B	Fred	01.10.2022	01.01.2023	60
C	Fred	01.12.2022	01.01.2023	5

As an output i eventually need an overview of hours needed per resource and month. Something like the table below (which i will use for other visuals too - but i am sure to handle that part):

	October	November	December	Jan
Martin	30	20	20	0
Fred	20	20	25	0

My question is, how can i best transform the data to get the results needed? Do I need to transform it at all, or can i use some clever filtering/aggregation?

Thanks.

[Anonymous]

Hi @LtKvasir ,

Could you please provide some calculate logic about you needs? This is very useful for me.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu

[LtKvasir]

Hi,

thanks for the reply. I am not quite sure, what you mean by calculate logic.

However, I think I found something to try in the meantime. I'll expand the rows to 1 row per day within the time intervall and than use that data to do the analysis. I had hoped there would be a more elegant way, but it will work i guess.

Thanks for your effort.

[Anonymous · Accepted solution]

Hi @LtKvasir ,

I'm a little confused about your needs, Could you please explain them further? It would be good to provide a screenshot of the results you are expecting and desensitized example data.

Thanks for your efforts & time in advance.

Best regards,
Community Support Team_ Binbin Yu