Change Month to the Following Month based on Day

[Chipsahoy1]

Hi All,

Hoping someone can help me with something.

I am trying to update the month of a date column based on if the day is >= to the 5th calendar day.  If it is, I would like the date in the new column to show the following month and year if applicable. 

One example, is if the date is 1/6/25, I would like the date column to show 2/1/25.  If the date is 1/3/25, I would like the date in the new column to show 1/1/25.

The instance where having both the month and year updated is for example the date is 12/7/24.  I would like new date column to reflect 1/1/25, but if the date is 12/4/24, I would like the new date column to have 12/1/24 listed.

Hope that makes sense and thank you in advance for your help!

[rsbin]

@Chipsahoy1 ,

Please try this as your new Calculated Column:

NewMonth = SWITCH(
               TRUE(),
               [DayOfMonth] >= 5, EOMONTH( [Date], 0 ) + 1, 
               STARTOFMONTH( YourDateTable[Date] ))

I trust you are using a Calendar or Date Table in your model.

Regards,

[Chipsahoy1]

Thank you for the quick response.  I used your formula, but it's also updating anything that has a calendar day <5 as well.  In the screenshot below, it's updating the 1/1/25 to 2/1/25 instead of listing it as 1/1/25.

[Chipsahoy1 · Accepted solution]

I figured it out.  the formula should be 

Test = SWITCH(

TRUE(),

[Initiative Actual Implementation Date].[Day] >= 5, EOMONTH( [Initiative Actual Implementation Date], 0 ) + 1,

STARTOFMONTH( Table_query[Initiative Actual Implementation Date]))

[rsbin]

@Chipsahoy1 ,

Yes that's the modification I was going to suggest.   You beat me to it.

Nicely done.

Trust you have what you need now.

Best Regards,

[Chipsahoy1]

Thanks for your help rsbin!  I really appreciate it!

[rsbin]

@Chipsahoy1 ,

Are you sure you are using the correct data columns?  Here are my results:

20241201	Sunday, December 1, 2024	12/1/2024	1	Sunday, December 1, 2024
20241202	Monday, December 2, 2024	12/2/2024	2	Sunday, December 1, 2024
20241203	Tuesday, December 3, 2024	12/3/2024	3	Sunday, December 1, 2024
20241204	Wednesday, December 4, 2024	12/4/2024	4	Sunday, December 1, 2024
20241205	Thursday, December 5, 2024	12/5/2024	5	Wednesday, January 1, 2025
20241206	Friday, December 6, 2024	12/6/2024	6	Wednesday, January 1, 2025
20241207	Saturday, December 7, 2024	12/7/2024	7	Wednesday, January 1, 2025
20241208	Sunday, December 8, 2024	12/8/2024	8	Wednesday, January 1, 2025
20241209	Monday, December 9, 2024	12/9/2024	9	Wednesday, January 1, 2025
20241210	Tuesday, December 10, 2024	12/10/2024	10	Wednesday, January 1, 2025
20241211	Wednesday, December 11, 2024	12/11/2024	11	Wednesday, January 1, 2025
20241212	Thursday, December 12, 2024	12/12/2024	12	Wednesday, January 1, 2025
20241213	Friday, December 13, 2024	12/13/2024	13	Wednesday, January 1, 2025
20241214	Saturday, December 14, 2024	12/14/2024	14	Wednesday, January 1, 2025
20241215	Sunday, December 15, 2024	12/15/2024	15	Wednesday, January 1, 2025
20241216	Monday, December 16, 2024	12/16/2024	16	Wednesday, January 1, 2025
20241217	Tuesday, December 17, 2024	12/17/2024	17	Wednesday, January 1, 2025
20241218	Wednesday, December 18, 2024	12/18/2024	18	Wednesday, January 1, 2025
20241219	Thursday, December 19, 2024	12/19/2024	19	Wednesday, January 1, 2025
20241220	Friday, December 20, 2024	12/20/2024	20	Wednesday, January 1, 2025
20241221	Saturday, December 21, 2024	12/21/2024	21	Wednesday, January 1, 2025
20241222	Sunday, December 22, 2024	12/22/2024	22	Wednesday, January 1, 2025
20241223	Monday, December 23, 2024	12/23/2024	23	Wednesday, January 1, 2025
20241224	Tuesday, December 24, 2024	12/24/2024	24	Wednesday, January 1, 2025
20241225	Wednesday, December 25, 2024	12/25/2024	25	Wednesday, January 1, 2025
20241226	Thursday, December 26, 2024	12/26/2024	26	Wednesday, January 1, 2025
20241227	Friday, December 27, 2024	12/27/2024	27	Wednesday, January 1, 2025
20241228	Saturday, December 28, 2024	12/28/2024	28	Wednesday, January 1, 2025
20241229	Sunday, December 29, 2024	12/29/2024	29	Wednesday, January 1, 2025
20241230	Monday, December 30, 2024	12/30/2024	30	Wednesday, January 1, 2025
20241231	Tuesday, December 31, 2024	12/31/2024	31	Wednesday, January 1, 2025
20250101	Wednesday, January 1, 2025	1/1/2025	1	Wednesday, January 1, 2025
20250102	Thursday, January 2, 2025	1/2/2025	2	Wednesday, January 1, 2025
20250103	Friday, January 3, 2025	1/3/2025	3	Wednesday, January 1, 2025
20250104	Saturday, January 4, 2025	1/4/2025	4	Wednesday, January 1, 2025
20250105	Sunday, January 5, 2025	1/5/2025	5	Saturday, February 1, 2025
20250106	Monday, January 6, 2025	1/6/2025	6	Saturday, February 1, 2025
20250107	Tuesday, January 7, 2025	1/7/2025	7	Saturday, February 1, 2025

[Chipsahoy1]

Not sure if this matters or not, but the date listed in the initiative actual implement column is a nested if statement combining 3 other date columns.  

This is how I used your formula.

Test = SWITCH(

TRUE(),

[Initiative Actual Implementation Date] >= 5, EOMONTH( [Initiative Actual Implementation Date], 0 ) + 1,

STARTOFMONTH( Table_query[Initiative Actual Implementation Date]))

Was this incorrect?