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...
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

     

     

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] )
)
)

  • sidds_sriv's avatar
    sidds_sriv
    Regular Visitor
    10 years ago

    Thanks Matt , it worked....

  • 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] )
    ))