powerbidaxdata-analysispowerbi-desktopcalculated-columns

Power BI Subtract 1 from column where date matches


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: enter image description here

Here is the link to the Power BI file: https://toffeeshare.com/c/ZD2Jp3ECSI

Here is an excel spreadsheet showing what the end result should look like. The orange column is the original week on the month, the yellow column is the adjusted week of the month where the first and last week is added to the adjacent week if there is 3 days or less in that week. The green column is what the end result should be.

enter image description here

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.


Solution

  • Try this:

    Column = VAR x =  CALCULATE( MIN('Date'[Adjusted Week Of Month]), ALLEXCEPT('Date', 'Date'[Month Year]))
    RETURN IF(x = 2, 'Date'[Adjusted Week Of Month] - 1,'Date'[Adjusted Week Of Month])