Finding Previous Month Sales

[Anonymous]

Hi Guys,

I am having a problem in calculating the previous month sales value for  the adjusted sales.

in my data model I have used left join to join orders and returns table with Order ID, the return table is having (Order ID, Status, Return Date) what is need is to find the adjusted sales which means the (Total sales- total sales of the product that have been returned), so I am able to find the adjusted sales for current month but I am not able to find the adjusted sales for previous month.

I am using these expressions:

MinDate = Min([Return Date])

MaxDate= Max([Return Date])

vPreviousYearRD= Year(AddMonths(Date(MinDate),-1)) // to find the year

vPreviousMonthRD=Month(AddMonths(Date(MinDate),-1))// to find month

vPreviousMonthMin = Min({<Month={'$(=vPreviousMonthRD'},Year={$(=vPreviousYearRD)}>}[Return Date])

vPreviousMonthMax= Max({<Month={'$(=vPreviousMonthRD'},Year={$(=vPreviousYearRD)}>}[Return Date])

Adjusted Sales= sum({<[Return Date] = {'>= $(=vPreviousMonthMin) <=$(=vPreviousMonthMax)'}>}Sales)

and I am using the filter of year and month which are generated using Order Date.

Can some one help me out in this please, it is urgent.

Thanks in advance,

Vivek

[#2]

Here

vPreviousYearRD= Year(AddMonths(Date(MinDate),-1)) // to find the year

vPreviousMonthRD=Month(AddMonths(Date(MinDate),-1))// to find month

Try using the MaxDate.

Hope this helps

[swuehl]

You may need to clear user selections in the calendar fields to avoid an incompatible selection state in your set expression:

sum({<[Return Date] = {'>= $(=vPreviousMonthMin) <=$(=vPreviousMonthMax)'}, [Order Date], Month, Year>}Sales)

[sunny_talwar]

In addition you might need to add Date() function to your variables or into your set analysis directly

vPreviousMonthMin = Date(Min({<Month={'$(=vPreviousMonthRD)'},Year={$(=vPreviousYearRD)}>}[Return Date]), 'DateFieldFormat')

vPreviousMonthMax= Date(Max({<Month={'$(=vPreviousMonthRD)'},Year={$(=vPreviousYearRD)}>}[Return Date]), 'DateFieldFormat')

Dates in Set Analysis

[Anonymous]

Hi Guys,

If I am using the expression Sum({<Month={'$(=vPreviousMonthRD)'},Year_RD={$(=vPreviousYearRD)}>}Sales) it is giving me the value for the selection made on year and month but if I select year=2010, month=jan it is giving me the value =0 , it should give me a value. How can I get it any suggestions.

Thanks,

Vivek

[swuehl]

Could you post a small sample app or at least post a complete scenario (like your selections you are doing, all resulting variable values, also the sample records that should be filtered by your set expression)?