Attached is a workbook that you might find helpful. I basically do what you ask with a calculated field, that evaluates to non-null only for the current month. Then you can use this as you wish, but bear in mind that this approach does no take into account any non-working days, i.e. it is an estimate based only on the days that passed since the beginning of the month.
Hope this helps.
EoM sales projection.twbx.zip 19.2 KB
thanks a bunch!
do you mind in english saying what the formula says?
i wish there was step into step out :/
no worries, here's the formula with some comments:
First of all, I am checking if the record falls into the current month. This is the first row, where I basically take the first day of the month from the [Date] field, and compare it with the first day of the month in the date returned by today() function:
if datetrunc('month',max([Date]))=datetrunc('month',today()) then
This evaluates to true only if [Date] belongs to the current month, otherwise it is some date in the past, and we should not estimate the result of a period that has passed already. Therefore I do not have an ELSE statement in the IF clause, i.e. ot returns Null, if the [Date] is outside the scope of the current month.
The second line basically does the "extrapolation" by taking the sum([Sales]) for the current month, multiplying by the number of days the month has in total, and then dividing by the number of days that have already passed. It is like that:
The second part of the nominator in this row: day(dateadd('month',1,datetrunc('month',max([Date])))-1) works like that:
1) datetrunc('month',max([Date]) takes the maximum date stored in the [Date] field, and rounds it down to the first date of the month - i.e. instead of 20 August 2013 it will return 01 August 2013.
2) Then I add 1 month to the result by dateadd('month',1,datetrunc('month',max([Date]))), to get the first date of the following month, and then subtract one day to get to the last day of the month. This is in order to get the last date of the month, which may have 28,29,30 or 31 days.
3) I return then as an integer the day number in order to know how many days in total are in the month of question.
In the denominator, what I have is actually the day number of the last day in the [Date] field. As a matter of fact if you do not have a record one day, this will give a false result, so you better substitute that last part with day(today()) instead.
The third row just closes the IF statement with an END.