Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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
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
How does this behave for the first week of the year?
Thanks
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] )
)
)
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....
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
105 | |
69 | |
67 | |
43 |
User | Count |
---|---|
148 | |
103 | |
103 | |
88 | |
66 |