cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Regular Visitor

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

 

 

 

1 ACCEPTED SOLUTION
Microsoft
Microsoft

Hi,

 

For example,  Your sales table is like below:

 

4.png

 

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

 

5.png

 

Best Regards

Alex

 

 

View solution in original post

6 REPLIES 6
Microsoft
Microsoft

Hi,

 

For example,  Your sales table is like below:

 

4.png

 

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

 

5.png

 

Best Regards

Alex

 

 

View solution in original post

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

 

Thanks

Thanks,
Victor Rocca | www.victorrocca.com

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.

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



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.

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

Thanks Matt , it worked....

Helpful resources

Announcements
secondImage

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

secondImage

Power BI Women

Join our monthly meetings and learning sessions.

secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors