sum to latest month

[alwayslearning]

Hi

I have a field named month which give me data in a dd/mm/yyyy format.  I then have a target column I want to just sum to latest month. i.e. if it is November there will be data for the full year but I want it to just show up to November.

One other thing is that I don't want this figure to change no matter what selection is made by the user from the various Listboxes I have.

Thanks

[shraddha_g]

If you Don't have YearStart Field then

sum({1<CalendarDate = {">=$(=Date(YearStart(Max(CalendarDate))))<=$(=Date(Max(CalendarDate)))"}>}Target)

else

sum({1<CalendarDate = {">=$(=Date(Max(YearStart)))<=$(=Date(Max(CalendarDate)))"}>}Target)

[Anil_Babu_Samineni]

Why can't simple set operator

And you should have month field from script

Month(Datefield) as Month

And then try to use this from your expression.

Sum({<Month = {'$(=Max(Month))'}>}Sales)

[alwayslearning]

Hi,

Both the above expressions return me the total year value and not up to the latest month.

I don't have a year start value. I do have a year field but it is not a date format and just a string yyyy

[alwayslearning]

Hi Anil,

Thanks for the help

This only returns me the latest month.  I want a sum of all month's up to the latest.

[Anil_Babu_Samineni]

Then use something like this

Sum({<Month = {>=Month(YearStart(Today())) <= Max(Month)}>}Sales)

[MK9885]

Use the below in Master Calendar script

If( Date > monthstart(addmonths(today(),-10)) and Date <= today(),1)

as [Rolling 11],

And in your expression use

=Sum({<[Rolling 11]={1}, your dimension/measure>}Sales)

Rolling 11 will only give you data only until Nov-2016.

[Anonymous]

Hi Kwok,

First convert that date into month like :- Date(DateField,'YYYY-MM')

now raise a flag like if(interval(date(today(),'YYYY-MM'),Date(DateField,'YYYY-MM'))=0,'CurrnetMonth','PreviousMonth') As MonthStatus

Now u can use this flag in your expression by giving filter MonthStatus='CurrentMonth' .

Thanks,

[shraddha_g]

Expression will give you values from year start to till date

If max date in your calendar data is 21st November then that expression will give you data from 1st jan till 21st November.

When you say upto November what does it actually mean?

Also check what is max date in your calendar?

[shraddha_g]

If your calendar has future dates,then it will consider those dates also while calculating max date.