How to get data from period - this month

[sculptorlv]

Hello! Hope for your help again.

I have columns "Posting_date" and "Sales".

How can I get all "Sales" values from current month?

I need to replace IF value "Bouth__PostingDate > Today()- 15" in the following sentence :

= SUM(   IF(PostingDate > Today()- 15, Sales, 0) )

Because - 15 is ok only, if we have 15.12.2015 date today. How can I make it ok for all TODAY dates?

Thank you in advance.

[Anonymous]

try like this:

=SUM({<PostingDate=,PostingDate={'>=$(=date(monthstart(max(PostingDate)),'YourDateFormat')<=$(=date(max(PostingDate),'YourDateFormat')'} >} Sales)

where 'YourDateFormat' like ='DD/MMM/YYYY'

also make sure PostingDate & 'YourDateFormat' format should be same

[Anonymous]

If you want to do for Max date only i.e today only, then try like this:

=SUM({<PostingDate=,PostingDate={'$(=date(max(PostingDate),'YourDateFormat')'} >} Sales)

where 'YourDateFormat' like ='DD/MMM/YYYY'

also make sure PostingDate & 'YourDateFormat' format should be same

[Anonymous]

check this for more details:

Set Analysis for certain Point in Time

[sculptorlv]

Something wrong this the syntax in this Date format, can you check please?

[jonathandienst · Accepted solution]

A simple correction to your statement:

Sum(If(PostingDate >= MonthStart(Today()), Sales, 0)

Converted to set analysis:

Sum({<PostingDate = {">=MonthStart(Today())"}>} Sales)

[sculptorlv]

Thank you!

I really was looking for this function MonthStart

[Anonymous]

Could you share your expression?

[sculptorlv]

=SUM({<Bouth__PostingDate=,Bouth__PostingDate={'>=$(=date(monthstart(max(PostingDate)),'YourDateFormat')<=$(=date(max(PostingDate),'YourDateFormat')'} >}  Bouth_Cofee_Item_Money)

[sculptorlv]

Is there opposite function to Addmonths?

___months(Date,1) = Date - 1 month