Forum Discussion
Weekly Index Year Ago
I have a few years of data that come in weekly. I want to create an Index Year Ago Column (100* Current Week value /52 Weeks Ago value). I've seen that the time intelligence functions work with days but not weeks.
I tried to create and link a date table (connected via the weekly date). Then I tried to pull the week that is offset by 52 weeks. This is not working.
Let me clarify if the week 9/21/2019 has a value of 2,000,000 and 52 weeks later the week ending 9/19/20 has a value of 2,200,000 I want the IYA in the 9/19/20 line to be 110. I don't want to base this on days because some years have 366 days and some 365.
Anonymous Please @ mention me in your next reply so I don't lose the thread.
Taking the MAX of your Date table will provide the DAX with the context of the current date in the table visual (you must use your Date table date in all your visuals and your DAX).
Make sure you:
1) Mark date table as Date table.
2) Use Date table date in the visual
3) See attached file below my signature of this post - you have a few options depending on which value you want to match to the week.
OPTION A - Last Year value using standard time intelligence measure and the End of Week column from Date table
Previous Year CovidMetricSum =CALCULATE([CovidMetricSum], DATEADD(DimDate[Date], -1, YEAR))OPTION B: Use Week of Year Number:
8 Replies
- amitchandak
Super User
Anonymous , Last year same week day is 364 days behing
with help from date table
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Year))
Other option with week and week rank
or
This year week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year same week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8- AllisonKennedy
Community Champion
amitchandak wrote:
Anonymous , Last year same week day is 364 days behing
with help from date table
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,Year))
amitchandak Won't this give the data for 364 years ago???
Anonymous Are you wanting the average weekly value? (I'm guessing this from your explanation being divided by 52:
(100* Current Week value /52 Weeks Ago value)
If so, you can use DATESINPERIOD and you don't need to worry about week time intelligence, just providing the Week Number from the Date Table inside the visual will do it.
WeeklyAvgRolling12MonthsMEASURE = CALCULATE ( [CovidMetricSum] , DATESINPERIOD( MAX(DimDate[Date]), -1, YEAR) ) / 52
Thank you for the reply. Since my dates span several years, I'm not sure what taking the max of my date table is going to do? Let me clarify. For every row, or date in the table, i want to take the sales in that week and divide by the same week year ago (Week Offset of -52 weeks). I don't know how to do this offset. I do have a date table, but my raw data is coming in weekly so I matched this to the week ending date in my Date table. However, I still don't know what to do next. Sorry I'm very new to PBI.
