Forum Discussion
Week On Week - Sales and Opportunities from SF Report
Objective:
Show the week on week change for both Sales and Number of Opportunities from a Salesforce Report. This dummy set only includes 2023 dates but the real dataset is from the 2021-2023. Multiple years also presents another issue to work around.
Table:
| Create Date (Date) | Week of Year (Int) | Opportunity Name (Text) | Sales USD (Fixed Decimal) |
| 01/01/23 | 1 | SF Company 1 | 500 |
| 01/02/23 | 1 | SF Company 2 | 300 |
| 01/03/23 | 1 | SF Company 3 | 800 |
| 01/08/23 | 2 | SF Company 4 | 200 |
| 01/09/23 | 2 | SF Company 5 | 700 |
| 01/10/23 | 2 | SF Company 6 | 500 |
| 01/16/23 | 3 | SF Company 7 | 400 |
| 01/17/23 | 3 | SF Company 8 | 100 |
| 01/18/23 | 3 | SF Company 9 | 500 |
| 01/19/23 | 3 | SF Company 10 | 700 |
Objective Results (Sales)
| Week | Sales This Week | Sales Last Week | Delta |
| 1 | $1,600 | $1600 | |
| 2 | $1,400 | $1,600 | $-200 |
| 3 | $1,700 | $1,400 | $300 |
Objective Results (Opportunity)
| Week | Ops This Week | Ops Last Week | Delta |
| 1 | 3 | 3 | |
| 2 | 3 | 3 | 0 |
| 3 | 4 | 3 | 1 |
Hi,
Create a Calendar Table with a week number column. Create a relationship (Many to One and Single) from the Date column of the Data table to the Date column of the Calendar table. To your visual, drag Week number from the Calendar Table. Write these measures
Total = sum(Data[Sales USD])
Total in previous week = calculate([Total],datesbetween('Calendar'[date],min('calendar'[date])-7,min('calendar'[date])-7)))
Variance = [Total]-[Total in previous week]
Hope this helps.
5 Replies
- Ashish_Mathur
Super User
Hi,
Create a Calendar Table with a week number column. Create a relationship (Many to One and Single) from the Date column of the Data table to the Date column of the Calendar table. To your visual, drag Week number from the Calendar Table. Write these measures
Total = sum(Data[Sales USD])
Total in previous week = calculate([Total],datesbetween('Calendar'[date],min('calendar'[date])-7,min('calendar'[date])-7)))
Variance = [Total]-[Total in previous week]
Hope this helps.
Hi Ashish,
Thanks for the reply, this seems to work with Week #. Is there a way I can modify this to work with the Week of Year intead of the Week Num. This would let me account for multiple years as it looks like Week Num is pulling from all years. I've tried modifying the Total Previous Week for Week of Year, however it's not returning correctly.
Total Previus Week of Year =CALCULATE([Total],DATESBETWEEN('Calendar'[Week of Year],MIN('Calendar'[Week of Year])-1, MIN('Calendar'[Week of Year])-1))
Thanks again!- Ashish_Mathur
You are welcome. I do not understand. What is the difference between week number and week of year? Show the download link of the file and show the expected result very clearly.
