Date hierarchy for Week

[jeongkim]

Hi,

Is it possible making week level grouping? 

[Daniel29195 · Accepted solution]

@jeongkim 

you need to create a week calculated column in your date table. ( either end of week, either start of week ,  either W conatenated with the week number ) . 

then you can simply add this week column in your hierarchy  . ( the week column can be created in powr query ). 

honestly i dont memorize the way it can be created, but you can simply ask chatgpt or any other Ai chat tool, and it willl write the code for you . ( Note here, that you should not always rely on Ai tools ,  but for this specific need that would be fine ) . 

if you dont have a date table, i would also recommend to create one . and then link your date table to your fact table.  

DAX Date Template - SQLBI

All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI - RADACAD

Note : 
base on the screenshot,  i assume that you are using the auto/date  time from power bi .  

if that so, i would recommend to turn off that options . ( this can be turned off from the options of power bi . 

article for reference: Automatic time intelligence in Power BI - SQLBI

hope that helps  you out with your problem .

[v-lgarikapat · Accepted solution]

Hi @jeongkim ,

Thanks for reaching out to the Microsoft fabric community forum.

@MasonMA , @Daniel29195  , @pallavi_r ,

Thanks for your prompt response.

I’ve implemented the Date hierarchy for the Week level and uploaded a sample PBIX file for your review. Kindly take a look and let me know if it’s working as expected on your end.

Looking forward to your feedback.

If this post helped resolve your issue, please consider the Accepted Solution. This not only acknowledges the support provided but also helps other community members find relevant solutions more easily.

We appreciate your engagement and thank you for being an active part of the community.

Best regards,
LakshmiNarayana

 

[v-lgarikapat]

Hi @jeongkim ,

I wanted to follow up and confirm whether you’ve had a chance to review the information we shared. If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know  we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi Narayana

[v-lgarikapat]

Hi @jeongkim ,

I wanted to follow up and confirm whether you’ve had a chance to review the information we shared. If you have any questions or need further clarification, please don’t hesitate to reach out.

If you're still encountering any challenges, feel free to let us know we’d be glad to assist you further.

Looking forward to your response.

Best regards,
Lakshmi Narayana

[v-lgarikapat]

Hi @jeongkim ,

As we haven't heard back from you, we are closing this thread. If you are still experiencing the issue, please feel free to create a new thread we’ll be happy to assist you further.

Thank you for your patience and support.

If you found our response helpful, please mark it as Accepted Solution and consider giving a Kudos, so others with similar queries can find it easily.

Best Regards,

Lakshmi Narayana

[MasonMA]

@jeongkim 

For me the most efficient way is creating my Date table in Power Query with Melissa de Korte's ultimate Date function! 

Her date function basically has everything a calendar date table is supposed to have. Choose whatever columns you need for now and you can add any other columns you need later from PQ whenever you need and then integrate that in your date hierarchy in Power BI if you need. 

Super convenient and I'd recommend you give it a try:) 

[Daniel29195 · Accepted solution]

@jeongkim 

you need to create a week calculated column in your date table. ( either end of week, either start of week ,  either W conatenated with the week number ) . 

then you can simply add this week column in your hierarchy  . ( the week column can be created in powr query ). 

honestly i dont memorize the way it can be created, but you can simply ask chatgpt or any other Ai chat tool, and it willl write the code for you . ( Note here, that you should not always rely on Ai tools ,  but for this specific need that would be fine ) . 

if you dont have a date table, i would also recommend to create one . and then link your date table to your fact table.  

DAX Date Template - SQLBI

All in One: Script to Create Calendar Table or Date Dimension using DAX in Power BI - RADACAD

Note : 
base on the screenshot,  i assume that you are using the auto/date  time from power bi .  

if that so, i would recommend to turn off that options . ( this can be turned off from the options of power bi . 

article for reference: Automatic time intelligence in Power BI - SQLBI

hope that helps  you out with your problem .

[pallavi_r]

Hi @jeongkim 

Please see if using weeknum dax function can help you achieve this. Please see the below youtube link for reference.

https://learn.microsoft.com/en-us/dax/weeknum-function-dax

https://www.youtube.com/watch?v=rqpcqVw1KI4

If this post helps, please accept this as a solution. Appreciate your kudos.

Thanks,

Pallavi