Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to Solution.
@jonathandhay 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
OPTION B: Use Week of Year Number:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@jonathandhay , 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8
When I tried your Dax I'm not getting last years values, I'm still getting the value from the current week?I'm not sure what's going wrong?
Similarly, the other function which ties back to my date table only put one value in for the year and it was same weeks value instead of the value year ago
@amitchandak wrote:
@jonathandhay , 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???
@jonathandhay 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
https://dax.guide/datesinperiod/
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
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.
@jonathandhay 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
OPTION B: Use Week of Year Number:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
If your Date table has both a WEEKNUM and YEAR column, you can calculate your measure where Year = Year -1 and Weeknum values match.
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
This is exactly what I'm trying to learn how to do in Dax. Do you have a code snipit? I don't know how to say grab the value where the year is year -1 same week.
HI @jonathandhay,
I suppose his formula may be like this, you can take a look at the below formula if it helps:
measure =
VAR currDate =
MAX ( Table[Date] )
RETURN
CALCULATE (
'calculate expression',
FILTER (
ALLSELECTED ( Table ),
YEAR ( [Date] ) = YEAR ( currDate ) -1
&& WEEKNUM ( [Date], 2 ) = WEEKNUM ( currDate, 2 )
)
)
Regards,
Xiaoxin Sheng
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 |
---|---|
109 | |
102 | |
84 | |
79 | |
68 |
User | Count |
---|---|
120 | |
110 | |
94 | |
81 | |
77 |