5 Replies Latest reply: May 25, 2016 4:23 PM by Stefan Wühl RSS

    Finding Previous Month Sales

    vivek Moningi

      Hi Guys,

       

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

       

      export.png

      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