Add Week to date hierarchy

[cmacdonald16]

I am building a report and want to display average hours worked in a week by an employee (week= MOnday to Sunday)

I have all the raw data, but my date Hierarch only shows Year, Quarter, Month, Day.

Does anyone know how i can add week?

or go abpout figuring out the above?

[Anonymous · Accepted solution]

Hi @cmacdonald16 ,

You need to create a custom hierarchy. Create a date table with week columns and then create custom hierarchy

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

Custom Hierarchy
https://stoneridgesoftware.com/creating-hierarchies-in-power-bi/
https://www.youtube.com/watch?v=x6vXVJZ_eTY

I have also found a helpful post, please refer to it to see if it helps you.

Solved: Adding week to date hierarchy - Microsoft Power BI Community

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.