Forum Discussion

telston's avatar
telston
New Member
5 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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's avatar
amitchandak
Super User
5 years ago

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-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8