Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Help with customizing a weekly chart using a Date Dimension

I am trying to create charts that show the total counts of data by the week. I was successfully able to set something up by using a date range and then creating a weekly column with:

= Table.AddColumn(#"Added Custom1", "End of Week", each Date.EndOfWeek([Date]), type date)

---

So my columns look something like this:

powerPat_0-1640627559453.png

However, I need the first week to be February 1st and some of the week counts are slightly off from where I want them to be. Is there an easy fix or workaround for this issue? Would I need to write in some step manually for each week of the year? I would appreciate any insight or a point in the right direction. 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

Hi,

 

Thank you for the suggestion. I ended up solving this issue by creating a long Switch statement like seen on this stackoverflow post: https://stackoverflow.com/questions/40254578/multiple-if-statements-in-dax

 

However, I am open to accepting a more efficient solution. Here is how the first few lines of my switch statement look:

new_weeks = SWITCH(TRUE(),
AND('Date Dimension (4)'[Date]>DATE(2021,1,31), 'Date Dimension (4)'[Date]<DATE(2021,2,6)), 1,
AND('Date Dimension (4)'[Date]>DATE(2021,2,5), 'Date Dimension (4)'[Date]<DATE(2021,2,13)), 2,

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

Hi,

 

Thank you for the suggestion. I ended up solving this issue by creating a long Switch statement like seen on this stackoverflow post: https://stackoverflow.com/questions/40254578/multiple-if-statements-in-dax

 

However, I am open to accepting a more efficient solution. Here is how the first few lines of my switch statement look:

new_weeks = SWITCH(TRUE(),
AND('Date Dimension (4)'[Date]>DATE(2021,1,31), 'Date Dimension (4)'[Date]<DATE(2021,2,6)), 1,
AND('Date Dimension (4)'[Date]>DATE(2021,2,5), 'Date Dimension (4)'[Date]<DATE(2021,2,13)), 2,
Anonymous
Not applicable

Hi @Anonymous ,

 

Glad you can solve the issue.

Please mark your reply as an answer, and more people will benefit.

 

 

Best Regards,

Stephen Tao

 

 

jennratten
Super User
Super User

If you are needing to use a non-standard calendar for your calculations, you can incorporate it by adding new, mapping table with one row per week, one column containing the week number and another column containing the first date of the week.  Merge the two tables (table 1 = the table above, table 2 = the mapping table, join on the date, join kind = left outer join).  Expand the column with the join results to add the week number from the mapping table.  Then select the week number column and fill-down.  Based on your specific requirements, this may also be able to be implemented with DAX.  Here is a good article on week-related calculations.

 

https://www.daxpatterns.com/week-related-calculations/

 

 

If this post helps to answer your questions, please consider marking it as a solution so others can find it more quickly when faced with a similar challenge.

Proud to be a Microsoft Fabric Super User

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.