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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Jo77
Helper I
Helper I

DAX - Daily cumulative sum based on scoring order

Hello,

 

I have created the table below as an example of what I need to do. I would like to create a calculated column that behaves like the last column (i.e. I have the first 5 columns and the last one is what I want to create through DAX).

In the Fact table below there are employees (EmployeeID) that work daily on projects (ProjectID). Each project has a unique global scoring. For each date the effort per project is converted to a Resource Allocation % per project (already done in the table below for simplicity). I would like to create a cumulated daily resource allocation per employee, but it needs to be sorted by the project scoring. Hope the table explains it. I think the WINDOW function is the way to go but not sure.

OPTIONAL: the project scoring is a global number contained in a different Dim table where each project is (DIM_Project). I've included the project scoring in the table below to make it easier, and also because I don't know whether my ask may work without the scoring in the same table. If the answer could contain 2 solutions, one for the situation where the project scoring is in the same table as per below and one where the project scoring is in a different table as in the reality it would be much appreciated. I would prefer the one with the scorim in the other table but if it is not possible then with the scoring in the same table is fine.

Thank you very mcuh in advance for your help, really appreciated 🙂

 

EmployeeIDAllocationDateProjectIDProject ScoringResource Allocation (%)Daily Running ResAll (%)
121-Jul-24638133851010
121-Jul-24764798354050
121-Jul-247648001550100
122-Jul-24638133851010
122-Jul-24764798354050
122-Jul-247648001550100
123-Jul-24638133851010
123-Jul-24764798358090
123-Jul-247648001550140
124-Jul-24638133851010
124-Jul-24764798358090
124-Jul-247648001550140
220-Jul-24638133852020
220-Jul-24764798354060
220-Jul-247648001550110
221-Jul-24638133852020
221-Jul-24764798354060
221-Jul-247648001550110
222-Jul-24638133852020
222-Jul-24764798354060
222-Jul-247648001550110
223-Jul-24638133851010
223-Jul-24764798358090
223-Jul-247648001550140
224-Jul-24638133851010
224-Jul-24764798358090
224-Jul-247648001550140
1 ACCEPTED SOLUTION
Rakesh1705
Super User
Super User

Source Data

Rakesh1705_0-1721578666129.png

Sequence number to sort project scoring keeping employee id and date intact.

 

Rakesh1705_1-1721578735929.png

Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.

Rakesh1705_2-1721578812346.png

If this solves your problem then please accept the same as your solution.

View solution in original post

9 REPLIES 9
Rakesh1705
Super User
Super User

Source Data

Rakesh1705_0-1721578666129.png

Sequence number to sort project scoring keeping employee id and date intact.

 

Rakesh1705_1-1721578735929.png

Cumulative daily allocation calculation based on the sequence number (ascending) for a fixed date and emp id.

Rakesh1705_2-1721578812346.png

If this solves your problem then please accept the same as your solution.

Thanks @Rakesh1705 , much appreciated

I think the intermediate step of the order is not needed. It can work directly with the scoring (also needs the bigger than to change as a consequence). Like this:

Prioritised Resource Allocation (%) =
VAR CurrEmplID = FACT_ResourceAllocation[EmployeeID]
VAR CurrDate = FACT_ResourceAllocation[AllocationDate]
VAR CurrScore = FACT_ResourceAllocation[Project score]
RETURN
CALCULATE(SUM(FACT_ResourceAllocation[Resource Allocation (%)]), FILTER(FACT_ResourceAllocation,FACT_ResourceAllocation[EmployeeID]=CurrEmplID), FILTER(FACT_ResourceAllocation,FACT_ResourceAllocation[AllocationDate]=CurrDate), FILTER(FACT_ResourceAllocation,FACT_ResourceAllocation[Project score]>=CurrScore))
I need to give it a better look but in this way I think it works. Thanks for the pointer, really appreciated!

@Jo77 thank you for your kind words

Irwan
Super User
Super User

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 (%)]
)

Irwan_0-1721514713278.png

 

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):

Jo77_0-1721522961039.png

 

hello @Jo77 

 

do you mind to copy and paste your sample data so i can copy your sample data? not in image format.

 

You can certainly use WINDOW, but first thing is you need to explain your need/goal/limitation in calculation.

 

So, I am going to clarify your need.

You want to calculate daily cumulative of Resource Allocation for each EmployeeID for each PPM-ID for same Project Score? This will reset when different employeeID, different PPM-ID, and different Project Score. And if project score is different, it will be calculated differently?

Is this correct?

 

Or you just want to daily cumulative for different Project Score only? This will reset when Project score is different.

 

Sorting Project Score can do later with indexing or sort in visualization.

 

 

 

Thank you.

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.

Jo77_0-1721564662544.png

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 

Apologies @Irwan but I'm trying to post the data. It shows as posted but then it is not. If I refresh or if I look at it from my phone it is just not there. Not sure how to do it, I'll keep trying.

Here you have it I was getting some HTML issue that I cannot get around. I'm sending it in a way that hopefully you can use the space as separator to put it back together. Apologies, thanks again for your help @Irwan.

 

EmployeeID        AllocationDate   PPM-ID Project score      ResourceAllocation          Resource Allocation (%)

100003 21/07/2024       638133 58.2       0.75       10

100003 21/07/2024       764800 48           3.75       50

100003 21/07/2024       764798 35.25    3             40

100003 22/07/2024       638133 58.2       0.75       10

100003 22/07/2024       764800 48           3.75       50

100003 22/07/2024       764798 35.25    3             40

100003 23/07/2024       638133 58.2       0.75       10

100003 23/07/2024       764800 48           3.75       50

100003 23/07/2024       764798 35.25    3             40

100003 24/07/2024       638133 58.2       0.75       10

100003 24/07/2024       764800 48           3.75       50

100003 24/07/2024       764798 35.25    3             40

100003 25/07/2024       638133 58.2       0.75       10

100003 25/07/2024       764800 48           3.75       50

100003 25/07/2024       764798 35.25    3             40

100003 26/07/2024       638133 58.2       0.75       10

100003 26/07/2024       764800 48           3.75       50

100003 26/07/2024       764798 35.25    6             80

100003 27/07/2024       638133 58.2       0.75       10

100003 27/07/2024       764800 48           3.75       50

100003 27/07/2024       764798 35.25    6             80

100005 21/07/2024       638133 58.2       1.5         20

100005 21/07/2024       764800 48           2.25       30

100005 21/07/2024       764798 35.25    1.875    25

100005 22/07/2024       638133 58.2       1.5         20

100005 22/07/2024       764800 48           2.25       30

100005 22/07/2024       764798 35.25    1.875    25

100005 23/07/2024       638133 58.2       1.5         20

100005 23/07/2024       764800 48           2.25       30

100005 23/07/2024       764798 35.25    1.875    25

100005 24/07/2024       638133 58.2       1.5         20

100005 24/07/2024       764800 48           2.25       30

100005 24/07/2024       764798 35.25    1.875    25

100005 25/07/2024       638133 58.2       1.5         20

100005 25/07/2024       764800 48           2.25       30

100005 25/07/2024       764798 35.25    1.875    25

100005 26/07/2024       638133 58.2       1.5         20

100005 26/07/2024       764800 48           2.25       30

100005 26/07/2024       764798 35.25    1.875    25

100005 27/07/2024       638133 58.2       1.5         20

100005 27/07/2024       764800 48           2.25       30

100005 27/07/2024       764798 35.25    1.875    25

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.