Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 @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.
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!
Solved! Go to Solution.
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.
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
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.
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
@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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
131 | |
110 | |
96 | |
70 | |
67 |