To give you an idea of what I am doing I have a Calander table I created, and I am breaking down the weeks in a month… not weeks by year. In these weeks in a given month that range from 1-6 weeks per month I am making any week at the beginning or end of the month that have less than 4 days in that week for the month are being rolled into the week before or the week after in that month.
For example, if the beginning week (week 1) of the month only has 3 days or less I am changing that week from week 1 to week 2, also if the ending week (week 5 or 6) of the month has 3 or less days I am changing that week from week 5 to week 4 or from week 6 to week 5.
Here is what I need help with:
I want to create another column that will look at the beginning of the month and see if the “Adjusted Week of Month” starts with week 1 or is missing week 1, if it is missing week 1 then I want to subtract 1 from the column “Adjusted Week of Month” for the months that do not have a week 1.
Here is a picture of the table:
I do not know how to post the exported excel document here.
Please let me know if you need anymore information and Thank you ahead of time for anyone that can help.
You can upload your .pbix to a sharing site and then share a link here. Can you add some expected results as the before and after are not clear at the moment.