11 Replies Latest reply: Oct 13, 2016 12:54 PM by Sunny Talwar

# Number of days in a month

Hello,

I am trying to calculate the no of days in a particular month, and in my case it is not the normal calendar month rather a Fiscal month.

Details are attached here.

 Fisc Period Sales end of Fisc period No of Fiscal days 201605 8909.19 28 201606 4938.3 35 201607 10999 28 201608 5487 28 201609 11878 35 201610 5489 8

in the above table, the last column is based on the Fisc_day_of_month column in the Calendar sheet.

• ###### Re: Number of days in a month

I am confused, is the above table given or are we looking to calculate No of Fiscal Days field in the script?

• ###### Re: Number of days in a month

Hello Sunny,

We have the column "Fisc_day_of_month" in the Calendar, so all we have to do is to get the Max of every Fiscal_Period. And if we are in the middle of the month, like today Oct 11th, we will have to get the date from the Sales tab of the excel and get the corresponding Fisc_Day_of_month number.

you will get a better picture if you take a look at the different tabs of the attachment.

• ###### Re: Number of days in a month

May be something like this:

Day(Max(Fisc_day_of_month))

• ###### Re: Number of days in a month

No Sunny, it would not give me the right output. In the excel attachment, i have the calendar which has the Fisc Dayof Month numbers for our regular calendar dates. And i just want to pick the highest number for every Fiscal Period. And only for October, we are on 11th Oct, but the most recent entry for Oct is on 9th Oct in the Sales tab, so we will have to pick the Fisc number of 9th Oct.

• ###### Re: Number of days in a month

Something like this?

Table:

Fisc_day_of_month,

Fisc_Period

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Calendar);

Left Join (Table)

Max(Fisc_day_of_month) as [No of Fiscal days]

Resident Table

Where Date < Today()

Group By Fisc_Period;

Sales:

Sales,

Ctry

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Sales);

• ###### Re: Number of days in a month

Thanks for the suggestion Sunny.

However the calendar table is joined already with adifferent field to my fact tables. Is there a way to achieve this at the front end?

• ###### Re: Number of days in a month

Using this expression:

=Max({<Date = {"\$(='<' & Date(Today()))"}>}Fisc_day_of_month)

• ###### Re: Number of days in a month

Thanks for the suggestions Sunny, It seems to work when we display it against the Fisc Period, but it does not work when i use along with different expressions. e.g. FirstSortedValue function is used in the attached sample, to get the latest Sales of every Fisc Period and then it should be divided by the No of days in that Fisc Month. Please take a look at the attached sample.

• ###### Re: Number of days in a month

Does this look right?

I changed your expression to this

=Max({1<Date = {"\$(='<' & Date(Today()))"}>}Fisc_day_of_month)

Now the no of days won't change based on any selection you make. Does this look what you were looking for? Or was there another issue?

• ###### Re: Number of days in a month

I was looking for this solution, thanks Sunny. But what is happening if i bring in another dimension say, Country to the block. It changes back to the wrong values.

• ###### Re: Number of days in a month

You just need to provide all the details and we should be able to provide you the exact expression you need.

Try this:

=Max(TOTAL <Fisc_Period>{1<Date = {"\$(='<' & Date(Today()))"}>}Fisc_day_of_month)