Forum Discussion
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:
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.
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,
3 Replies
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,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/
