Forum Discussion
Helper IWEEKLY TRENDS comparison
Hi, I have daily data entry I just want to get the weekly trends comparison example my data is
| Date | Quantity |
| 1-Jan | 1 |
| 2-Jan | 1 |
| 3-Jan | 1 |
| 4-Jan | 1 |
| 5-Jan | 1 |
| 6-Jan | 1 |
| 7-Jan | 1 |
| 8-Jan | 1 |
| 9-Jan | 1 |
| 10-Jan | 1 |
| 11-Jan | 1 |
| 12-Jan | 1 |
| 13-Jan | 1 |
| 14-Jan | 1 |
| 1-Feb | 2 |
| 2-Feb | 2 |
| 3-Feb | 2 |
| 4-Feb | 2 |
| 5-Feb | 2 |
| 6-Feb | 2 |
| 7-Feb | 2 |
| 8-Feb | 2 |
| 9-Feb | 2 |
| 10-Feb | 2 |
| 11-Feb | 2 |
| 12-Feb | 2 |
| 13-Feb | 2 |
| 14-Feb | 2 |
I want my result to show like this if i select the date filter . is it possible to put it in graph as well? please help. Thank you:)
| Month | Week | Total |
| Jan 1-Jan 7 | week 1 | 7 |
| Feb 1- Feb 7 | week 1 | 14 |
| Jan 8-Jan 14 | week 2 | 7 |
| Feb 8- Feb 14 | week 2 | 14 |
Hi jayceeb ,
In Power BI Desktop, if you want to use a DAX statement after selecting a date, you need to strictly follow the Week rule on the calendar to do so.
WeeklyTotal = CALCULATE( [TotalQuantity], ALLEXCEPT('Table', 'Table'[MonthYear], 'Table'[WeekNumber]) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 Replies
- amitchandak
Super User
jayceeb , You have create week columns in you table
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],1)+1 //Sunday
Week Start date Monday = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1 //Monday
Week End date = [Week Start date] +6
format and use in other columnYou can check here
https://www.youtube.com/watch?v=cyWVzAQF9YU&t=40648s
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/1187482
https://medium.com/chandakamit/cheat-sheet-any-weekdays-week-start-date-just-one-variable-apart-6b2e6f593958Power 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/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8 - Hi jayceeb,
I create a table as you mentioned.
Then I create two calculated columns and here are the DAX codes.
WeekRange =VAR StartOfWeek ='Table'[Date] - WEEKDAY ( 'Table'[Date], 2 ) + 1VAR EndOfWeek = StartOfWeek + 6RETURNFORMAT ( StartOfWeek, "MMM d" ) & " - "& FORMAT ( EndOfWeek, "MMM d" )WeekNumber ="week "& WEEKNUM ( 'Table'[Date], 2 ) - WEEKNUM ( STARTOFMONTH ( 'Table'[Date] ), 2 ) + 1Next I think you can create a new table.
WeeklyTrends =SUMMARIZE('Table','Table'[WeekRange],'Table'[WeekNumber],"Total", SUM('Table'[Quantity]))Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
jayceebhi i got it however the date is not arrange.
i use the sorting but still mixed. and in the table wek1 and week 6 is the same date. i also use filter for the dates. how to arrange the date please.
Hi jayceeb ,
In Power BI Desktop, if you want to use a DAX statement after selecting a date, you need to strictly follow the Week rule on the calendar to do so.
WeeklyTotal = CALCULATE( [TotalQuantity], ALLEXCEPT('Table', 'Table'[MonthYear], 'Table'[WeekNumber]) )Best Regards
Yilong Zhou
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
