A Power BI common request from business users is to do weekly calculations. This can become tricky, especially if there are anomalies in the cadence of reporting dates for the data. In this example for the 30-year mortgage rate data from FRED, they typically published the data on Friday in the early 1980s (currently they do it on Thursday), but that wasn't always the case. During this illustrated time period in the fall of 1981, they occasionally reported the results on other days, like a Wednesday or a Thursday. We can't use the DATEADD function here as a filter to move the time intelligence ruler back on a seven day interval to determine the previous week's rate because it's not always exactly seven days earlier. One solution for this problem is to tap into the new Enhanced DAX Time Intelligence options that came out in the September Power BI updates last week. It allows us to do week-over-week and week-to-date running totals using built-in DAX functions. This feature is currently in preview, however, and we also need to configure the calendar options in a specific way for it to work. It's one approach that can save us a lot of potential headaches, even if it takes more work to set up. If you work with modeling and analysis on a weekly basis, I definitely recommend checking out these new enhanced DAX time intelligence functions when you get a chance. #PowerBI #TimeIntelligence #AlwaysBeLearning
Thanks for sharing!
What is the advantage of this approach compared to using a week offset column in your calendar table?
Great insight! Weekly calculations can definitely be a headache when the data cadence isn’t consistent. These new Enhanced DAX Time Intelligence functions sound like a real game-changer — especially for handling irregular reporting dates. Can’t wait to try them out in my next Power BI project!
Thanks for sharing!
Data Analyst
1moThis is great and perfect timing considering how many time intelligence items are always needed