Days in Month
Hello Everyone,
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.
Thanks!
Best Answer
-
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.3
Answers
-
I believe the function WeekDay() will be your friend. Take a look at the below and see if this helps you out.
Notice, I turned summary to None for the Weekday line item as sums are not needed.
Hope this helps,
Rob
1 -
HI jedge@dsi.com,
Check out this!
Use the function 'Daysinmonth'. Screenshots below,
Note: Make the summary as 'Average' and refer this line item in your Month module.
I hope this helps!
Regards,
Kavin.
2 -
Hi,
This Can be achieved without using any function also, My Approach is I hard coded day count as 1.
Notice, You have used Time Dimension in WEEK format, so you won't get the correct number of calendar days instead you will get the number of days based out of the week.
Note: You can turn the summary off for certain line items if you don't need it.
Hope it helps,
Prabhu 🙂
0 -
Some great solutions posted already!
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.
I hope this helps!
Thanks,
Usman
7 -
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?
0 -
Hi Chris,
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.
Kind Regards,
Bram Kurstjens
1 -
Very valid point!
This is a great solution and what I will always go to first.
But it is better to have a scalable solutions as the best practice suggests in the planual, so users don't have to do manual tasks.
Thanks,
Usman
0 -
After **** my head against the wall this is what I ended up doing. Of course this year is a leap year so I will have to change it next year.
0 -
I know this is an old thread, but I needed to do the same thing recently and really didn't want to go the manual maintenance route, nor did I want an overly complex solution, so here's what I did:
Step 1: Create 2 line items using MONTH and YEAR to pull the month # and year # from a month. Using MID will ensure you don't get caught in the 4-4-5 rollup.
Step 2. Create a 3rd line item using DAYSINMONTH along with the year # and month # from above to get the correct number of days for each calendar month.
2
