Number of days in a month

[#1]

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.

Please let  me know if more information is needed.

[sunny_talwar]

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

[#3]

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.

[sunny_talwar]

May be something like this:

Day(Max(Fisc_day_of_month))

[#5]

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.

[sunny_talwar]

Something like this?

Table:

LOAD Date,

    Fisc_day_of_month,

    Fisc_Period

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Calendar);

Left Join (Table)

LOAD Fisc_Period,

  Max(Fisc_day_of_month) as [No of Fiscal days]

Resident Table

Where Date < Today()

Group By Fisc_Period;

Sales:

LOAD Date,

    Sales,

    Ctry

FROM

[Max date.xlsx]

(ooxml, embedded labels, table is Sales);

[#7]

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?

[sunny_talwar]

Using this expression:

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

[#9]

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.

[sunny_talwar]

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?