Forum Discussion
Week On Week - Sales and Opportunities from SF Report
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.
Super UserHi,
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 =
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.
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.