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

[kkanukurthi]

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

[Anonymous · Accepted solution]

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.

[BeaBF]

@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

[kkanukurthi]

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...

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

[BeaBF]

@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