topic Re: Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week) in DAX Commands and Tips

Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week)

kkanukurthi — Fri, 07 Jun 2024 12:09:07 GMT

Hello everyone, Looking for a Dax for two calculated columns. Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week).

I have a Date Table from 01/01/2023 to 12/31/2025. I need two columns. Column1 should show previous/before month end last Saturday in date format. Column2 should show following/next month end last Friday date. For example, for row1, if the previous/before month end Saturday fall on 29th April 2023, column1 date should be 29/04/2023 and column2 should show following/next month end Friday date i.e May 26/05/2023.

And for row2 , the column1 date should be +1 of above Friday date( (26/05/23)+1=27/05/23) i.e Saturday date 27/05/2023 and column2 should be following/next month end Friday date i.e June 30/06/2023. I need to see the difference between these two columns dates. It should repeat though out the date table from year 2023 to 2025 in two columns. For above example, row1 difference shows 28 days and row 2 shows difference of 35 days. The expected/required output shown below.

Column1. Column2. Days

Saturday Friday

Row1. 29/04/2023 26/05/2023 28 days

Row2. 27/05/2023(+1date of row1 column2 friday) 30/06/2023 35 days

Row3. 01/07/2023(+1date of row2 column2 friday) 28/07/2023 28 days

Similarly, for all months of 2024

Row4. 29/06/2024 26/07/2024 28 days

Row5. 28/07/2024(+1date of row4 column2 friday) 30/08/2024. 34 days

Similarly, for all months of 2025

Row6. 04/01/2025 31/01/2025 28 days

Row7. 01/02/2025(+1date of row6 column2 friday) 28/02/2025 28 days

Note : Saturdays taken here are not every month end saturday. Some Saturdays may will fall in same month also. All saturday dates should start with immediate next of above column 2 friday date(+1 of Friday end date).

Please help me with Dax calculated columns for 3 years. Appreciate your help in advance.Thanks

Re: Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week)

BeaBF — Fri, 07 Jun 2024 12:55:54 GMT

kkanukurthi Hi!

Here the three calculated columns that u need to calculate on your Calendar Table:

NextMonthEndLastFriday =

VAR EndOfNextMonth = EOMONTH('Calendar'[Date], 1)

VAR DayOfWeek = WEEKDAY(EndOfNextMonth, 2) -- 2 means Monday = 1, Sunday = 7

VAR Offset = IF(DayOfWeek = 6, 1, DayOfWeek + 2)

RETURN EndOfNextMonth - Offset

PreviousMonthEndLastSaturday =

VAR EndOfPrevMonth = EOMONTH('Calendar'[Date], -1)

VAR DayOfWeek = WEEKDAY(EndOfPrevMonth, 2) -- 2 means Monday = 1, Sunday = 7

VAR Offset = IF(DayOfWeek = 7, 1, DayOfWeek + 1)

RETURN EndOfPrevMonth - Offset

DifferenceInDays =

DATEDIFF('Calendar'[PreviousMonthEndLastSaturday], 'Calendar'[NextMonthEndLastFriday], DAY)

BBF

Re: Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week)

kkanukurthi — Fri, 07 Jun 2024 13:31:24 GMT

Hi BeaBF ,

Thanks for the response. The solution you provided is different from expected output. PLease check the link, the dates are wrong.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Month-Start-on-EOM-Saturday-last-end-week-of-before-month-and/m-p/3979927#M154304

For example : If we are in Feb 2024

previousMonthend last saturday should be

saturday Next monthend Friday should be

27/01/2024 23/02/2024

24/02/2024(+date of friday date) 29/03/2024

30/03/2024(+1date of friday date) 26/04/2024

Please check and do needful. Thanks

Re: Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week)

BeaBF — Fri, 07 Jun 2024 15:22:23 GMT

kkanukurthi here the updated formulas:

PreviousMonthEndLastSaturday =

VAR PrevMonth = EOMONTH('Calendar'[Date], -1)

VAR LastDayPrevMonth = DATE(YEAR(PrevMonth), MONTH(PrevMonth), DAY(EOMONTH(PrevMonth, 0)))

VAR LastSaturday = LastDayPrevMonth - WEEKDAY(LastDayPrevMonth) + IF(WEEKDAY(LastDayPrevMonth) >= 6, -1, 0)

RETURN LastSaturday

NextMonthEndLastFriday =

VAR PreviousMonthEndLastSaturday = [PreviousMonthEndLastSaturday]

VAR NextMonthStartDate = EOMONTH(PreviousMonthEndLastSaturday, 1) + 1

VAR NextMonthEndDate = EOMONTH(NextMonthStartDate, 0)

VAR LastDayOfMonth = NextMonthEndDate - DAY(NextMonthEndDate) + 1

VAR DaysUntilFriday = MOD(6 - WEEKDAY(LastDayOfMonth), 7)

VAR NextMonthLastFriday = LastDayOfMonth - DaysUntilFriday

RETURN

IF(DaysUntilFriday = 0, NextMonthLastFriday - 7, NextMonthLastFriday)

BBF

Re: Month Start on EOM Saturday(last/end week) of before month and end on next EOM Friday(last/end week)

Anonymous — Mon, 10 Jun 2024 07:53:14 GMT

Hi kkanukurthi

Thanks for the reply from BeaBF , please allow me to provide another insight:

Create two calculated columns as follow:

Column1 = VAR CurrentDate = 'Table'[Date] VAR FirstDayCurrentMonth = DATE ( YEAR ( CurrentDate ), MONTH ( CurrentDate ), 1 ) VAR LastDayPreviousMonth = EOMONTH ( FirstDayCurrentMonth, -1 ) VAR DayOfWeek = WEEKDAY ( LastDayPreviousMonth, 2 ) VAR Adjustment = IF ( DayOfWeek >= 6, DayOfWeek - 6, - ( - 7 + 6 - DayOfWeek ) ) RETURN IF ( YEAR ( 'Table'[Date] ) = 2023 && MONTH ( 'Table'[Date] ) = 1, LastDayPreviousMonth - Adjustment, CALCULATE ( MAX ( 'Table'[Column2] ) , FILTER ( 'Table', MONTH ( 'Table'[Date] ) = MONTH ( LastDayPreviousMonth ) && YEAR ( 'Table'[Date] ) = YEAR ( LastDayPreviousMonth ) ) ) + 1 )

Column2 = VAR CurrentDate = 'Table'[Date] VAR LastDaynextMonth = EOMONTH(CurrentDate, 1) VAR DayOfWeek = WEEKDAY(LastDaynextMonth, 2) VAR Adjustment = IF(DayOfWeek < 5, - (- 7 + 5 - DayOfWeek), DayOfWeek - 5) RETURN LastDaynextMonth - Adjustment

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.