Source Data
Sequence number to sort project scoring keeping employee id and date intact.
Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.
If this solves your problem then please accept the same as your solution.
Source Data
Sequence number to sort project scoring keeping employee id and date intact.
Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.
If this solves your problem then please accept the same as your solution.
hello @Jo77
please check if this acomodate your need.
create a calculated column.
Cumulative =
SUMX(
FILTER(
'Table',
'Table'[EmployeeID]=EARLIER('Table'[EmployeeID])&&
'Table'[ProjectID]=EARLIER('Table'[ProjectID])&&
'Table'[AllocationDate]<=EARLIER('Table'[AllocationDate])
),
'Table'[Resource Allocation (%)]
)
For project scoring, form what i can see in your sample data, project scoring is same for projectID (i.e 764800 always has project scoring 15). Unless this is wrong, the cumulative can be calculated from projectID.
Hope this will help you.
Thank you.
Thank you very much @Irwan , really appreciate your time
Unfortunately it doesn't do what I would need. At the bottom of this response you can see how what it does is to cumulate by project and then reset when the EmployeeID changes (I've sorted the columns in the same way as in my original example for comparison). As per my original example, every day should reset the cumulation that is done project after project within the day for a given EmployeeID. Every EmployeeID has its own count too in theory, but the EmployeeID doesn't really matter other than to make sure that the allocations within a day don't keep cumulating regardless of the EmployeeID.
Also, to your query on the scoring, it is very important that the cumulation follows the order of priority of the scoring (higher scoring = higher priority). You can see that in my original example. To give you further context, the next step I want to do is to tag the rows where the cumulation is more than 100. This will mean that the employee will not be able to attend to that project that day. This is why the scoring (=priority of the project) is important. Only the lowest priority projects should get the highest numbers of cumulation as they should be at the bottom of the proiority pile. That's why originally I was maybe thinking about the WINDOW-type of function as they allow the ORDERBY/PARTITIONBY type of parameters, but I cannot make it work.
Hope the above helps explain. If you please look at my original example with this in mind hopefully it makes more sense. A big massive thank you again @Irwan
This is the result of inputting your code (scoring is different as this comes from the actual data, the one before was modified slightly for simplicity):
Hello @Irwan , thanks again
To not exceed the limits of the post I will put the explanation on the objective here in this post and then reply to it with the data if that's ok.
The objective is to have a way to know which projects cannot be done because they have employees in them that are overallocated (i.e. that they are above their 100% allocation). I put an improved view of my example below, it is a pic to not use characters and exceed the limit of the post.
The columns in green is the basic info. The ones in red are the ones I already have but, for this purpose, bring to the wrong conclusion. The ones in gray is where I want to go (only asking for the first gray column here in the forum).
As you can see in the red columns, I have a way to know the daily % allocation of an employee and therefore whether he/she is overallocated on that day. However, I cannot then go and tag the projects by the IsOverallocated column as it is not the real view. It is not because the projects that fit in the less than 100% allocation will get actually done, only the ones with a cumulative allocation % beyond 100% on the day will not get done. Which ones are they though? Here is where the project priority comes in and why the cumulative sum of daily allocation % per employee and day needs to be done in the project scoring order. To make sure that the ones that go above 100% are the ones with the lower priority and then only those are tagged in the column NEW IsOverallocated as being part of the overallocation.
Hope that explains it better, and shows why I have the concept of windows, sortby, partitionby in my head as the potential solution (from my small SQL days). Sorry for not having gone to this depth before and another massive thank you for your great help @Irwan
Helpful resources
Fabric Community Update - July 2025
Find out what's new and trending in the Fabric community.
Power BI Monthly Update - July 2025
Check out the July 2025 Power BI update to learn about new features.
