Forum Discussion
View totals by week
Hello,
I have an EventDate field that I am displaying some metrics on a line graph. Right now it has the EvenDate Month hierarchy on the axis. I would like to display it by week, starting Sunday through Saturday. So the user enters a date range and it breaks it down by week. How can I do this?
5 Replies
- amitchandak
Super User
Anonymous , You can join your date with date table and there you can create week and use that in the filter
columns like
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],1)
Week Number = WEEKNUM([Date],1)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)You can create a custom Hierarchy if needed
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-functions2.Any Weekday Week - Start From Any day of Week
https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Power-BI-Turning/ba-p/1187482Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123Thank you for your response. I do have a dimDate calendar table I am linking my EventDate to, I just didn't have the formula for Week. If I put Week on my axis, two things. 1. It doesn't seem to be sorting right and 2. Instead of showing the year and week number, can I show the date of the first day, like week of June 7th, june 14th, june 21st, etc?
v-zhenbw-msftCommunity Support
Hi Anonymous ,
If you want to show the first day in a week, you need to create a weeknum column and a start date column.
Please refer the following formula.
Weenum = WEEKNUM('Table'[Date],2)weenum_star_date = CALCULATE(MIN('Table'[Date]),FILTER('Table','Table'[Weenum]=EARLIER('Table'[Weenum])))The result like this,
If it doesn’t meet your requirement, could you please show the exact expected result based on the table that we have shared?
Best regards,
Community Support Team _ zhenbw
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
BTW, pbix as attached.
- SteveCampbell
Memorable Member
You should use a date table which contains this sort of information.
You can find many pre designed ones in the community, here is an example of one:
