Calculating a Ratio based on Current Day of Month
Hello,
The client would like to view forecasted, budgeted and PY data based on a ratio that takes into account current day of the month for Periodic and YTD data.
Below, is a functioning calc.
Use Case: it is October 22nd, and the client is using daily uploads for Sales. To get a more accurate view of the Sales compared to the scenarios listed above, they want to multiply the monthly total by the factor relative to the day of the month. E.g. if Sales is 100,000 for the October budget (periodic), but it is October 22nd, we would multiply (100,000 by (22/31)), the cube view would show 70,967.74. The calculation in a UD8 member above represents this logic.
The tragic flaw is that if view the data in YTD view, it takes the entirety of the YTD amount by that ratio, when I only want the current month to apply the ratio.
What would be the best way to approach this?
Ideas:
- Use a ratio that uses total days of year up to end of current period
- E.g. In October 22nd, 295 total days have passed. The total for October, is 304 days.
- Multiply the YTD Amount by (295/304)
- Calculate current period ratio then add in the Prior Period YTD amount
I've come up with a solution, and it was neither of the options above. More similar to option 2, but still different.
Essentially, I wanted to stay within the same period, not reading prior (where extra logic would be necessary in M1). So, I am essentially subtracting the periodic difference from the YTD amount.
E.g.
Budget YTD Amt = 100
Budget Per Amt = 10
Period Ratio = .70
Budget Calc Amt (Budget Per Amt * Period Ratio) = 7
Budget Per Amt - Budget Calc Amt = 3
Budget YTD Amt - 3 = 97
