Monthly vs Quarterly allocation of accounts
Hello,
We are trying to automate a simple allocation of specific corporate expense accounts. The accounts allocate to different business based on their contribution to total GP. I currently have two calculations:
- Account Dynamic Calc - BU GP / Total GP, giving the business GP%
- UD8 Dynamic Calc - GP% * Corporate Expense Account, giving the expense to be allocated to each BU
I run into an issue when data is pulled as either QTD or YTD. As an example, if someone is to pull the data at 2025M3/QTD, it will calculate calculate a separate GP% for the QTD information and complete the calculation. This creates a discrepancy when looking at data monthly vs. quarterly. Instead, I would need it to add together months 1-3 for consistency. (See below table for reference)
Is there a way to make a dynamic calculation calculate only at the monthly level and instead aggregate months for other view selections (QTD/YTD)?
| What is happening | What I need | ||||
| Calculation | QTD GP * QTD GP% | M1 + M2 + M3 | |||
| MTD | M1 | M2 | M3 | Q1 / M3 QTD | Q1 / M3 QTD |
| Business GP | 100,000 | 400,000 | 200,000 | 700,000 | 700,000 |
| Total GP | 500,000 | 1,000,000 | 900,000 | 2,400,000 | 4,300,000 |
| GP % | 20.0% | 40.0% | 22.2% | 29.2% | |
| Corporate Expense | 10,000 | 50,000 | 20,000 | 80,000 | |
| Allocation | 2,000 | 20,000 | 4,444 |
23,333 (29.2% * 80,000) | 26,444 (M1 + M2 + M3) |