MonthEnd - 7 day sales

[vireshkolagimat]

Hi All,

I am trying to get MonthEnd-7 days sales and i created the below expression. But in order to see last 7 day sales i have make selections. How i can make it fixed one.

=sum({<DateKey={">=$(=Date(Max(DateKey-6)),'YYYYMMDD')<=$(=Max(DateKey))"}>}SalesValue*SalesQty)

I am not looking for last 7 days but but it should be like monthend -7 days sales for each month.

If i choose Jan month, then it should show the Jan month sales(it looks fine) and last 7 days sales of Jan month.

The data should look like below.

Division	Jan Sales	Last 7 Day Sales
Consumer	1,000.000	30.000

Thanks for the support.

Regards,

Viresh

[swuehl]

There is a function Monthend() to get the end of the month, use something like

=sum({<DateKey={">=$(=Date(Monthend(Max(DateKey))-6,'YYYYMMDD'))<=$(=Date(Monthend(Max(DateKey)),'YYYYMMDD'))"}>}SalesValue*SalesQty)

edit: Changed first search date

[swuehl]

And remember to clear user selections in calender fields (other date fields) you need to ignore (like week, Date, Day):

=sum({<DateKey={">=$(=Date(Monthend(Max(DateKey))-6,'YYYYMMDD'))<=$(=Date(Monthend(Max(DateKey)),'YYYYMMDD'))"}, OtherDateOrWeekField= >}SalesValue*SalesQty)

[vireshkolagimat]

Thank you. It looks good.

[vireshkolagimat]

Yeah I did. thank you.