New model builder here. I'm trying to figure out how to display the amount of calendar days in month. I used DAYS() but it's coming back as 28 days 28 days 35 days which I presume is because we are in the 4-4-5 timescale. Is there a formula that I can use to figure out the days for a current period? In EXCEL it would be EOMONTH function.
My end goal is transpose our monthly forecast into weekly buckets. In order to do that I have to divide the monthly forecast by days, assign each day in the forecast period a daily forecasted sales amount then roll them up into weeks. I've done it in excel and while it takes a while to load it does the trick.
Hm, this is interesting. I tried playing around with the daysinmonth functionality originally. I was hoping by identifying the month/year that it would display the numeric value but I guess that isn't the case? When I did this in excel my formula was (CELL WITH MONTHLY FORECAST) / DAY(EOMONTH(CELL WITH MONTH/YEAR,0)) By using your methodology below, it might more more sense to just keep it in days, add the SKU codes as columns and divide the monthly forecast by the days thereby assigning each day a forecast value per SKU. I could then just use a SUM function for the days in the weekly period. Thoughts?
If your model is in the 445 week format you need to create a formula which identifies the real month start (calendar month start) and real month end (Calendar month end) then work out the number of days by taking the real start from the end. This will help to distinguish between the 445 week formated months.
In a monthly time dimensioned module create the following line items:
Real Month Start - formula - DATE(YEAR(ITEM(Time), MID), MONTH(ITEM(Time), MID), 1)
Real Month End - formula - ADDMONTHS(Real Month Start, 1) - 1
Real Month Days - formula - Real Month End - Real Month Start
I believe this will solve your issue and give you the correct calendar days in a month.
Many of the answers are trying to crack a nut with a sledge hammer. Simply create a module dimensioned by Month time period, a line item called days in month, formatted as number and manually enter the days. This would take you no time and would be easy to maintain as the data will only change during leap years.
You can then lookup from this table to feed any calculations.
Chris Heathcote Bedford Consulting
Gold Partner and Regional Partner of the Year 2021, EMEA
In general I like your reply, I usually jump into the complex formulas to fix a simple issue if there is no other functional way to do it. Your solution has limited size implications, probably works a little faster, and above all, is much easier to understand.
With such solutions it is important to either create a time range that covers a very large amount of years or to have a standard 'Yearly Update' documentation stating that one should update these months whenever a new year is added. Debugging a model after switching a year can be a very tiresome process!
Also be wary if you are working with ALM, typically I forget to update these standard data settings across Dev - Test - Prod.