Forum Discussion
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
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 Anonymous ). 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.
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!
Hi Anonymous ,
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.
To only show the latest number of weeks, you could add the [date] column to visual filter then use Relative date filter feature.
Best Regards,
Jay
3 Replies
Anonymous , 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
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..
Hope this clarifies my question a bit more.
Hi Anonymous ,
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.
To only show the latest number of weeks, you could add the [date] column to visual filter then use Relative date filter feature.
Best Regards,
Jay
