Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to Solution.
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:
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:
Hi @Anonymous ,
Glad you can solve the issue.
Please mark your reply as an answer, and more people will benefit.
Best Regards,
Stephen Tao
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