How to add week to hierarchy

[Supipa]

Hi all, 

I'm quite new with DAX and struggling to find a guide on how to add week to the date hieararchy. Can someone shed some light? Thank you!

[amitchandak · Accepted solution]

@Supipa , 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

[Supipa]

Hi @amitchandak , Thank you very much!

I've ended up creating a calendar table by using

 CALENDAR(DATE(YEAR(MIN(Transaction_facttbl[Date])),1,1),DATE(YEAR(MAX(Transaction_facttbl[Date])),12,31))

and then just adding a column for weeknum, but will try what you suggested too.

Thank you very much.