topic How to add week to hierarchy in DAX Commands and Tips

How to add week to hierarchy

Supipa — Thu, 21 Oct 2021 09:00:36 GMT

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!

Re: How to add week to hierarchy

amitchandak — Thu, 21 Oct 2021 16:59:38 GMT

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

Re: How to add week to hierarchy

Supipa — Fri, 22 Oct 2021 07:34:07 GMT

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.