Forum Discussion

sidds_sriv's avatar
sidds_sriv
Regular Visitor
10 years ago
Solved

Last 2 weeks sales

Hi,

 

I have a sales data on daily basis for each store-sku combinations....From this i am creating weekly report. For each week, we are taking sum of sales, etc...The data start from week number 6.

 

Now for each I would like calculate the Last 2 weeks sales. For example for week 8 i want to calculate the sales of week 6,and 7.  For week 9, i want to calculate the sales of week 7 and 8 ...so on and on...

 

Need Help on how to have this data in the report.

 

Thanks and Regards

Siddhartha Srivastava

 

 

 

Need Help
  • AlexChen's avatar
    AlexChen
    10 years ago

    Hi,

     

    For example,  Your sales table is like below:

     

     

    You can create a measure to calculate the sum of salesAmount of weeknum 6 and 7:

     

     last2WeeksAmount = CALCULATE(sumx(sales, sales[salesAmount]),Filter(ALL(sales), sales[weeknum]=max(sales[weeknum])-1 || sales[weeknum] = MAX(sales[weeknum])-2))

     

     

    Best Regards

    Alex

     

     

6 Replies

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    You need a calendar table with a unique Week ID column. Read about that here 

    http://exceleratorbi.com.au/power-pivot-calendar-tables/

     

    As as long as you have a unique week ID column, you can write a formula like this 

     

    Last 2 Weeks :=
    CALCULATE (
    [Total Sales],
    FILTER (
    ALL ( Calendar ),
    Calendar[WeekID] >= MAX ( Calendar[WeekID] ) -1
    && Calendar[WeekID] <= MAX ( Calendar[WeekID] )
    )
    )

    • uksrik's avatar
      uksrik
      Regular Visitor
      8 years ago

      Awsome, That was a cool trick Matt!!!

      I used used your logic to calculate Current Week Measure:

       

      CALCULATE(
      'TableQuery'[Sales US($)],
      FILTER (
      ALL ( 'Sales US($)' ),
      [TableQuery] >= MAX ( 'TableQuery'[Fiscal Year and Week] )
      ))

  • AlexChen's avatar
    AlexChen
    Microsoft Employee
    10 years ago

    Hi,

     

    For example,  Your sales table is like below:

     

     

    You can create a measure to calculate the sum of salesAmount of weeknum 6 and 7:

     

     last2WeeksAmount = CALCULATE(sumx(sales, sales[salesAmount]),Filter(ALL(sales), sales[weeknum]=max(sales[weeknum])-1 || sales[weeknum] = MAX(sales[weeknum])-2))

     

     

    Best Regards

    Alex

     

     

    • sidds_sriv's avatar
      sidds_sriv
      Regular Visitor
      10 years ago

      Thanks Alex, it worked...really appreciated...

       

      Just wanted to know if I need to caclulate Last 2 week sales for a particular product/category...for example for Car/Gun then how should i change the query.

    • vrocca's avatar
      vrocca
      Advocate IV
      8 years ago

      How does this behave for the first week of the year? 

       

      Thanks