Forum Discussion
Displaying data as week commencing in a chart
I want to show the week comencing on the x-axis (e.g. 2nd Mar, 9th Mar etc) but i only get day, month, quarter or year.
I know i can display it by week number but i want to see the actual weekly dates.
I have the w/c dates in a column in the table but still cannot get them to display.
Can anyone help please?
Hi,
Please try to create a calculated table first:
Date = CALENDAR(MIN('Table'[WC Date]),MAX('Table'[WC Date]))Then create one-to-one relationship between them.
Create a column to show all date's week NO. in this new date table:
WeekNo = ROUNDDOWN(DATEDIFF(MIN('Date'[Date]),'Date'[Date],DAY)/7,0)+MIN('Table'[Week])Then create a new column to format the date and weekNo:
Month-WeekNo = FORMAT('Date'[Date],"MMM")&"-"&'Date'[WeekNo]Choose a line chart to show the result:
Hope this can help.
Best Regards,
Giotto Zhi
6 Replies
Please find the file, I have created a week start date and week end date that can be used to display the chart. Also, Rank is used to display this week vs last week.
https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0
Not entirely sure what you want. I have Week Starting: https://community.powerbi.com/t5/Quick-Measures-Gallery/Week-Starting/m-p/391487#M123
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
Sorry but i dont understand your response.
I have the W/C data in a table with the rest of my data. I can display the data by week number but not by w/c.
WC Date Week Calls In Calls Ans Calls Ans (%) ❤️ mins
04-Nov-19 23 3129 2114 35.9% 11-Nov-19 24 2785 2206 36.3% 18-Nov-19 25 2522 2124 44.1% 25-Nov-19 26 2727 2390 46.8% 02-Dec-19 27 3620 2754 33.0% 09-Dec-19 28 4237 2796 22.1% 16-Dec-19 29 3682 2513 30.1% 23-Dec-19 30 1294 917 22.2% Either data is available daily and we are grouping weekly or data is available weekly with a date or Week no/year. What is true for you.
There can few other tables having data at day , month or all other levels.
We create a common Date dimension. In that we add Month, year, Week start, Week-End, Week No, Week year, etc. We join with all out other tables on date. I had given a file where both week start and week end date. So you can use that.
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hi,
Please try to create a calculated table first:
Date = CALENDAR(MIN('Table'[WC Date]),MAX('Table'[WC Date]))Then create one-to-one relationship between them.
Create a column to show all date's week NO. in this new date table:
WeekNo = ROUNDDOWN(DATEDIFF(MIN('Date'[Date]),'Date'[Date],DAY)/7,0)+MIN('Table'[Week])Then create a new column to format the date and weekNo:
Month-WeekNo = FORMAT('Date'[Date],"MMM")&"-"&'Date'[WeekNo]Choose a line chart to show the result:
Hope this can help.
Best Regards,
Giotto Zhi
