Week On Week - Sales and Opportunities from SF Report

[SeanPolley_Apty]

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

[Ashish_Mathur · Accepted solution]

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.

[SeanPolley_Apty]

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.

[SeanPolley_Apty]

That was a mistake on my end, Week of Year is correct you can ignore Week #.

Sample Dataset (One Year)
I was getting the correct values when I filtered down to just one year.

Year	Week of Year	Sales This Week	Sales Last Week	Delta
2022	1	$100		$100
2022	2	$150	$100	$50
2022	3	$50	$150	-$100

Sample Dataset (Multiple Years)
I believe the error was happening as it would calculate for Week of Year = 1 values including both 2022 and 2023.

Year	Week of Year	Sales This Week	Sales Last Week	Delta
2022	1	$100		$100
2022	2	$150	$100	$50
2022	3	$50	$150	-$100
2023	1	$100		$100
2023	2	$150	$100	$50
2023	3	$50	$150	-$100

However, I found a work around by creating a Year & Week of Year column (YYYY-WW) to sort chronologically and using the following OFFSET formula. From there it was a simple measure between This Week and Last Week to get a weekly delta.

Sales_LastWeek =

CALCULATE(

    [Sales_ThisWeek],

    OFFSET(

        -1,

        ALLSELECTED('Data'[Year & Week]),

        ORDERBY('Data'[Year & Week], ASC)))

Thanks again for taking the time to help me out. Sometimes just talking about it with others helps me realize things I wouldn't have thought of by myself.

[Ashish_Mathur]

You are welcome.