Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
NiekHacquebord
Frequent Visitor

Graph showing x-weeks rolling data over multiple years

Dear all,

 

I have been working on some data that is being reported on a weekly basis (i.e. each week is one data entry). All seemed to work fine until I wanted to refresh my data for the first time in 2022. I then got some strange results because of weeknumbers partly falling into both years, etc. 

 

Currently my view is as follows

volumes_PBI.png

 

I would like to have my x-axis showing the week numbers, but it should obviously 'reset' the count once the new year starts (as in below example by @v-rzhou-msft ). I tried some things (e.g. adding the year number to the week number to make it work in chronological order) but I haven't figured out the correct solution for now. 

 

1.png

 

Additionally, once my x-axis is fixed, I would like to show the data of the last x-number (26 or 52) of weeks, preferably updated automatically. 

 

Could anyone help me out with this? Thanks a lot!

1 ACCEPTED SOLUTION
v-jayw-msft
Community Support
Community Support

Hi @NiekHacquebord ,

 

Add a week column to the table.

week = WEEKNUM('Table'[Date],2)

Then use date heirarchy and [week] as x-axis and turn off concatenate labels under format -> x-axis.

1.PNG2.PNG

To only show the latest number of weeks, you could add the [date] column to visual filter then use Relative date filter feature.

3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.

View solution in original post

3 REPLIES 3
v-jayw-msft
Community Support
Community Support

Hi @NiekHacquebord ,

 

Add a week column to the table.

week = WEEKNUM('Table'[Date],2)

Then use date heirarchy and [week] as x-axis and turn off concatenate labels under format -> x-axis.

1.PNG2.PNG

To only show the latest number of weeks, you could add the [date] column to visual filter then use Relative date filter feature.

3.PNG

 

Best Regards,

Jay

Community Support Team _ Jay
If this post helps, then please consider Accept it as the solution
to help the other members find it.
amitchandak
Super User
Super User

@NiekHacquebord , use datesYTD

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))

or a measure like

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] <= Max('Date'[Week]) ))

 

 

Use a separate date or year week table 

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Hi Amit,

 

Thanks for your reply! Still haven't managed to make it work, but I am not sure whether I phrased my question correctly. What I tried previously is to use some kind of "YearWeek" column to fix my data (see below). However, the x-axis then has type "categorical", whereas I want to show it chronological order. Is there any way to fix this? Or should I maybe just number all the weeks over all the years and then use "continuous" on the x-axis? Then I probably won't be able to show correct values on the x-axis..

 

Capture.PNG

 

Hope this clarifies my question a bit more. 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.