Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
80 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |