Forum Discussion
How To Filter by Week Number
I am trying to apply a slicer to my data that would display the weeks per year all at the same time.
My data has each week's data, starting Monday through Sunday in each column for 2019, 2020 and 2021. I want to compare like weeks for all three years, so week 10 for all three years with an easy click of a slicer. I have attached a screenshot of my data structure. Is there a way to do this with the way my data is currently formatted?
Hi telston ,
Here are the steps you can follow：
1. Enter Power query through Transform data, select [Time] column, and select Transform – Unpivot Columns – Unpivot Other Columns.
Result:
2. Create calculated column.
Year = YEAR([Attribute])3. Create measure.
weeknumber = CALCULATE(DISTINCTCOUNT('Table'[Attribute]),FILTER(ALL('Table'),[Year]=YEAR(SELECTEDVALUE('Table'[Attribute]))))2019 = var _day=DAY(SELECTEDVALUE('Table'[Attribute])) var _month=MONTH(SELECTEDVALUE('Table'[Attribute])) return CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute]=DATE(2019,_month,_day)))2020 = var _day=DAY(SELECTEDVALUE('Table'[Attribute])) var _month=MONTH(SELECTEDVALUE('Table'[Attribute])) return CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute]=DATE(2020,_month,_day)))4. Result:
Use [Attribute] as a slicer to display.
Number of weeks per year.
Compare the data of the current selected date for all two years.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
telston , You have to unpivot this data
https://radacad.com/pivot-and-unpivot-with-power-bi
Then create a date table with week number, year, year week and week rank columns
new columns
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)Week = weeknum([Date],2)
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
OR
Week Rank = RANKX(all('Date'),'Date'[Year Week],,ASC,Dense) //YYYYWW formatthen try measure like examples
measures
This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last to last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -104 )))
This Year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))Last to last year = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-2 && 'Date'[Week] = Max('Date'[Week])))
Power 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=pnAesWxYgJ8Hi telston ,
Here are the steps you can follow：
1. Enter Power query through Transform data, select [Time] column, and select Transform – Unpivot Columns – Unpivot Other Columns.
Result:
2. Create calculated column.
Year = YEAR([Attribute])3. Create measure.
weeknumber = CALCULATE(DISTINCTCOUNT('Table'[Attribute]),FILTER(ALL('Table'),[Year]=YEAR(SELECTEDVALUE('Table'[Attribute]))))2019 = var _day=DAY(SELECTEDVALUE('Table'[Attribute])) var _month=MONTH(SELECTEDVALUE('Table'[Attribute])) return CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute]=DATE(2019,_month,_day)))2020 = var _day=DAY(SELECTEDVALUE('Table'[Attribute])) var _month=MONTH(SELECTEDVALUE('Table'[Attribute])) return CALCULATE(SUM('Table'[Value]),FILTER(ALL('Table'),[Attribute]=DATE(2020,_month,_day)))4. Result:
Use [Attribute] as a slicer to display.
Number of weeks per year.
Compare the data of the current selected date for all two years.
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
