Selecting previous month's purchases

[#1]

Hi All!.

I'm creating a model on stock ageing, and I need to pull through the Purchases for the previous calendar month into this model. I'm trying with this expression but I am getting zeroes;

=Sum({<DteGRN={">=$(=AddMonths(MonthStart(Max(num(Date))),-1))<=$(=AddMonths(Max(num(Date)),-1))"},TheMonth=, TheYear=>} TotalCost)

DteGRN is the date field in my PurchaseFact SQL Table that I am using, and hooking in a TimeDimension table as well.

Can anybody by anychance please help?

Many thanks!

[sunny_talwar]

What is the format of DteGRN field? Assuming it is MM/DD/YYYY, try this:

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'MM/DD/YYYY'))<=$(=Date(AddMonths(Max(Date), -1), 'MM/DD/YYYY'))"}, TheMonth=, TheYear=>} TotalCost)

Change to your date format accordingly.

[#3]

Thanks for your mail Sunny T - unfortunately did not work. This is what I used, changing the format to YYYY/MM/DD;

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'YYYY/MM/DD'))<=$(=Date(AddMonths(Max(Date), -1), 'YYYY/MM/DD'))"}, TheMonth=, TheYear=>} TotalCost)

[tamilarasu]

Hi Christopher,

Try this,

=Sum({<DteGRN={">=$(=Date(MonthStart(Max(Date), -1), 'YYYY/MM/DD'))<=$(=Date(MonthEnd(AddMonths(Max(Date), -1)), 'YYYY/MM/DD'))"}, TheMonth=, TheYear=>} TotalCost)

[stabben23]

Hi, Try this if you want to use Date:

Sum({<DteGRN={">=$(=MonthStart(addmonths(Max(Date), -1))<=$(=MonthEnd(AddMonths(Max(Date), -1))"}, TheMonth=, TheYear=>} TotalCost)

[#6]

Thanks, but I'm afraid it didn't work

[#7]

Thanks a mil, I'm afraid it didn't work

[tamilarasu]

I guess you need to format your date field DteGRN while loading the data. Try by changing your load script like below

Date(Date#(DteGRN,'Source file format'),'YYYY/MM/DD') as DteGRN

If that's not the problem, please post a sample.

[sunny_talwar]

Is DteGRN a date or timestamp? Would you be able to share a sample to look at this?