Days in Month

Highlighted
Occasional Contributor

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!

9 REPLIES 9
Highlighted
Master Anaplanner/Community Boss

Re: Days in Month

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.

 

2019-12-23_14-14-06.png

 Notice, I turned summary to None for the Weekday line item as sums are not needed.

2019-12-23_14-16-48.png

 

 

2019-12-23_14-14-34.png

 

Hope this helps,

 

Rob

Highlighted
Master Anaplanner/Community Boss

Re: Days in Month

HI jedge@dsi.com,

Check out this!

Use the function 'Daysinmonth'. Screenshots below,

image.png

 

Note: Make the summary as 'Average' and refer this line item in your Month module.

image.png

 

 

I hope this helps!

Regards,

Kavin.

Regards,
Kavin.
Highlighted
Occasional Contributor

Re: Days in Month

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?

Highlighted
Super Contributor

Re: Days in Month

Hi,

 

This Can be achieved without using any function also, My Approach is I hard coded day count as 1.

 

1.JPG

 

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.

 

2.JPG

 

Note: You can turn the summary off for certain line items if you don't need it.

 

Hope it helps,

Prabhu 🙂

Sorna Raja Prabhu
Highlighted
Master Anaplanner/Community Boss

Re: Days in Month

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

 

Highlighted
Frequent Contributor

Re: Days in 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.
Highlighted
Certified Master Anaplanner

Re: Days in Month

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

Highlighted
Master Anaplanner/Community Boss

Re: Days in Month

@ChrisHeathcote 

 

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

Occasional Contributor

Re: Days in Month

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.