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!

Accepted answers

ChrisAHeathcote

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.

All comments

rob_marshall

jedge@dsi.com ,

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

kavinkumar

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.

prabhu

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 🙂

usman.zia

Hi jedge@dsi.com

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

jedge@dsi.com

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?

ChrisAHeathcote

bram.kurstjens

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

usman.zia

@ChrisAHeathcote

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

jedge@dsi.com

After banging 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.

helennie

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.

blueprint_84441.jpg

result_84441.jpg

Quick Links

Can you help?

Unanswered questions

Create a Filter to show all the elements of that level based on selection in hierarchy
Hello Community members, Requirement Details: I have 5 levels of composite hierarchy list (A1, A2, A3, A4, A5). I have published a module in UX, Now the requirement is If user select a list member from A4 level in the context selector or Custom Filter I have to display all the list members under that A4 level. How can I…
import monthly data with months in columns
is there a way to import data with months in columns (usual excel table view)? I have to transform the data so there are rows to cover each period in the upload data form, but I am wondering if there is a better way to just import the data as is with the months in the columns. please let me know what you think. thank you,
Cloudworks Big Query Integration Fail
Hello, I am receiving an 'Anaplan Upload Failed' Status Description when testing my integration with a Big Query dataset. The integration imports data from BQ to our Anaplan model. No other details given in the error log. I suspect that Cloudworks is not even picking up the file but am not sure what we did wrong on the set…