Date Table with end of month variable

[rodrigooliveira]

Hi,

In my organization, the end of the month can change based on the last day of the month.

If the last day of the month is between Thursday and Friday, the month ends the next Saturday.

If the last day is between Sunday and Wednesday, the month ends on Saturday before.

Ex: November 2020 - Last day of the month is Monday, so the month ends the Saturday before (28th November). This means 29 and 30th November are part of December.

For December 2020: Last day of the month is Thursday, so the month ends the Saturday after (2nd January). This means 1st and 2nd January are part of December, and so on.

I'm using a simple date table, however, I don't if it is possible to integrate the month logic of my organization inside it.

Any help will be amazing, even if it is to say that is better to create a date table by hand.

Dates = 
Var BaseCalendar = 
    CALENDAR("1/1/2017", "31/12/2021")
RETURN
    GENERATE(
        BaseCalendar,
        Var BaseDate = [Date]
        Var YearDate = YEAR(BaseDate)
        Var MonthNumber = MONTH(BaseDate)
        Var WeekDay =  [Date]
        Var WeekNumber = WEEKNUM(BaseDate,21)
        //Var FiscalQuarter = QUARTER(5) //Regler Fiscal Year si différent de 1
        Var CalendarQuarter = QUARTER(BaseDate)
        Var SameDateLY = DATE(YEAR(BaseDate)-1,MONTH(BaseDate), DAY(BaseDate))
        Var SameDateBLY = DATE(YEAR(BaseDate)-2,MONTH(BaseDate), DAY(BaseDate))

        RETURN ROW(
            "Day", BaseDate,
            "Year", YearDate,
            "Month Number", MonthNumber,
            "Month", FORMAT(BaseDate, "mmmm"),
            "Year Month", FORMAT(BaseDate, "mmm yy"),
            "Day Week", FORMAT(WeekDay, "dddd"),
            "Week Number", WeekNumber,
            //"Fiscal Quarter", FiscalQuarter,
            "Calendar Quarter", CalendarQuarter,
            "Same Date Last Year", SameDateLY,
            "Same Date Before Last Year", SameDateBLY
            )
    )

[CNENFRNL · Accepted solution]

Bonjour, @rodrigooliveira , c'est un véritable casse-tête, n'est-ce pas? Essaie la formule dans une colonne calculée,

Month Adapted = 
VAR __weeknum = WEEKNUM ( [Date] )
VAR __day = DAY ( [Date] )
VAR __month_natural = MONTH ( [Date] )
VAR __eom = IF ( __day < 7, EOMONTH ( [Date], -1 ), EOMONTH ( [Date], 0 ) )
RETURN
    IF (//the very last week of a month
        __weeknum = WEEKNUM ( EOMONTH ( [Date], 0 ) ) //within the last week of a month
            || __weeknum = MOD ( WEEKNUM ( [Date] - __day ), 52 ), //weeknum of previous end of month
        IF (
            WEEKDAY ( __eom ) >= 5,
            MONTH ( __eom ),
            MONTH ( __eom + 1 )
        ),
        __month_natural
    )

[rodrigooliveira]

Merci beaucoup! Thank you!