How To Filter by Week Number

[telston]

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?

[Anonymous · Accepted solution]

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.

[amitchandak]

@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 format

then 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-La...
https://www.youtube.com/watch?v=pnAesWxYgJ8