I’m currently working on a financial reporting dashboard. We use a weekly 4-4-5 reporting calendar so can’t use the standard time intelligence functions. I’ve got a comprehensive dates table including indexes for Year/Period/Week.
My report page has a slicer to select the Year & Week (single field in the dates table) which drives the visual elements displayed on the page, such as tables and cards showing current week and period to date performance.
The problem I’m having is that I want to show an additional chart with a rolling 13-week trend for various measures (i.e. based on the Year/Week selected using the slicer, the chart needs to shows the range of 13 weeks from current selected week to -13 weeks).
Everything I’ve tried only shows up as a single week on the chart because the slicer is controlling the week for the whole page. We’ve had consultants in who have tried various dax formulas but nothing seems to be working. So far, we’ve only been able to calculate the total 13-week value for each measure, but when plotted it just displays as a single weekly value.
Does anyone have any ideas how to go about showing multiple weeks on the chart?
Hi @rob.walker. To help the forum members better visualize your issue and iterate possible solutions, please upload your work-in-progress PBIX along with a marked-up screenshot and/or Excel mockup showing the desired outcome for your visual of interest. If you need to mask your data, please check @BrianJ’s YouTube video:
Hi @rob.walker, as emphasized by @Greg above, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.
Use the forum search to discover if your query has been asked before by another member.
When posting a topic with formula make sure that it is correctly formatted to preformatted text </>.
Use the proper category that best describes your topic
Provide as much context to a question as possible.
Include the masked demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
As @Greg suggested, providing working/mock-up PBIX file always helps the forum members to provide the better solution in an efficient manner. But still to get things going you can check out the alternative provided below.
Sam Sir has already created a video which addresses this similar kind of issue. So I’m providing a link of that video as well as attaching the working of my practice file during my initial days. The only thing is, the measure shown in the video as well as calculated in my file are in terms of days but your team can modify the measure and change into the weekly scenario.
The measure in my file shows the results of both the scenarios. That is, “Values On Or Before Selected Date” as well as “Values After Selected Date”.
Below is the link of the video as well as working of the PBIX file attached for the reference.
Hoping you find this useful and meets your requirements that you’ve been looking for.
Hi @rob.walker, did the response provided by @Harsh help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. We’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!
Thanks for the response @Harsh. Sorry I haven’t responded sooner, it’s a large PBIX file with multiple years of our confidential financial data so trying to protect and share that with the forum would have been difficult.
I tried to implement the method Sam used in the link above, but ultimately we’ve managed to get it working using a date slicer table which hasn’t got a relationship with the Dates table. We set the MAX date based on the filtered Dates table and then calculate 13 weeks back to get the MIN.
However, we have had to use a continuous Week ID number (to allow the 13-week range to span two financial years) and we can’t display that field on the X-axis as it won’t mean anything to the end user.
When we do add in the text field [YearWeek], which is the one the end user would understand, it no longer works. This has been consistent with the many methods we’ve tried. As a work around we’ve hidden the x-axis and added a tooltip instead - not ideal but sort of works…
I’m glad that the trick showcased in the video sorted the things out although in an alternative manner.
For future reference, I’m providing the link below which was created by one of our EDNA Expert @BrianJ which showcases about how to mask the sensitive data. And than you can upload the masked file onto the forum wherein members and experts will be able to assist you in a better and efficient manner. Because without the file it just becomes the guess work for everyone.
