Date Table with end of month variable

rodrigooliveira — Fri, 11 Dec 2020 03:29:42 GMT

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

Re: Date Table with end of month variable

CNENFRNL — Fri, 11 Dec 2020 19:44:10 GMT

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 )

Re: Date Table with end of month variable

rodrigooliveira — Sat, 12 Dec 2020 00:23:10 GMT

Merci beaucoup! Thank you!

topic Date Table with end of month variable in DAX Commands and Tips

Date Table with end of month variable

Re: Date Table with end of month variable

Re: Date Table with end of month variable