Forum Discussion
Rolling Sum Using Virtual Table
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!
I was able to resolve this issue with the below code.
2 Replies
I was able to resolve this issue with the below code.
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
