Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
sidds_sriv
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
AlexChen
Employee
Employee

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
AlexChen
Employee
Employee

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

 

 

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 an 8 times 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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.