Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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.AddColu...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    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,
jennratten's avatar
jennratten
Super User
4 years ago

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/