Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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?
Solved! Go to 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.
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.
User | Count |
---|---|
107 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |