For the table of the values below:
| Date | Producer | Production rate(unit per day) | Number of Days |
|---|---|---|---|
| 16/1/2023 | 'A' | 1 | 31 |
| 16/1/2023 | 'B' | 5 | 31 |
| 16/1/2023 | 'C' | 10 | 31 |
| 16/2/2023 | 'A' | 2 | 28 |
| 16/2/2023 | 'B' | 6 | 28 |
| 16/2/2023 | 'C' | 11 | 28 |
| 16/3/2023 | 'A' | 3 | 31 |
| 16/3/2023 | 'B' | 7 | 31 |
| 16/3/2023 | 'C' | 12 | 31 |
How can I write a cumulative function to calculate the cumulative sum of the product of Production rate and Number of days in month for each producer.
The final table should be similar to below:
| Date | Producer | Monthly Production rate(unit per day) | Number of Days | CumSum |
|---|---|---|---|---|
| 16/1/2023 | 'A' | 1 | 31 | 31 |
| 16/1/2023 | 'B' | 5 | 31 | 155 |
| 16/1/2023 | 'C' | 10 | 31 | 310 |
| 16/2/2023 | 'A' | 2 | 28 | 87 |
| 16/2/2023 | 'B' | 6 | 28 | 323 |
| 16/2/2023 | 'C' | 11 | 28 | 618 |
| 16/3/2023 | 'A' | 3 | 31 | 180 |
| 16/3/2023 | 'B' | 7 | 31 | 540 |
| 16/3/2023 | 'C' | 12 | 31 | 990 |
Currently I am using this formula:
Sum([Number of Days] * [Monthly Production rate(unit per day)]) OVER (AllPrevious([Date]))
But not sure how to bring Producer into the equation. What is the best way to bring that one in please.
Use [Producer] to group by, so instead of
OVER (AllPrevious([Date]))
try
OVER Intersect([Producer],AllPrevious([Date])))