Rolling Sum Using Virtual Table

[dunna]

Hello,

I am struggling to get the desired output and could use some assistance if anyone can help. At a high level I am doing a rolling monthly sum but when a given month has an hours value higher than the total hours in a given month then it switches to the total hours in a month. There is a limitation with this has to be a calculated measure and cannot be done in PQ or with calculated tables/columns. 

When I put it into a table with the 'ID_TBL'[UUID] and 'Date'[Month Start Date] columns the measure result is just the ending months [Adjusted Monthly Hrs] value. The table below is an example of what the virtual table shows (first 5 columns) and then the actual output for 9/1/2024 (column 6) and what the desired/expected outcome to be (column 7)

UUID	Month Start Date	Hrs	Avail Hours	Adj Hrs	Rolling Sum	Desired Outcome
1	7/1/2024	661.00	744.00	661.00
1	8/1/2024	1,409.40	744.00	744.00
1	9/1/2024	666.50	720.00	666.50	666.50	2,071.50

Below is the calculated measure being used:

Thanks in advance for any hep, it is much appreicated!

[dunna · Accepted solution]

I was able to resolve this issue with the below code.

[Anonymous]

Hi,@dunna 
We are delighted that you have found a solution and are willing to share it.

Accepting your post as the solution is incredibly helpful to our community, as it enables members with similar issues to find answers more quickly.

Thank you for your valuable contribution to the community, and we wish you all the best in your work.

Best Regards,

Leroy Lu