Need help in creating a custom Date Calendar

[_mittalhimanshu]

Hi All,

I have a date and time field in my dataset. I need create a date calendar which can accomodate the following.

1. Weekday starts from Thursday to Wednesday. 

2. WeekNum should be month wise and yearwise both.
3. Month should be considered on the basis of maximum days present.

[Kedar_Pande]

@_mittalhimanshu 

Create Date Calendar Table:

DateCalendar = 
VAR MinDate = MIN(YourDataTable[DateColumn])
VAR MaxDate = MAX(YourDataTable[DateColumn])
RETURN
ADDCOLUMNS(
CALENDAR(MinDate, MaxDate),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"WeekDay", WEEKDAY([Date], 2), // Custom week starting from Thursday (2 = Mon-Sun, so adjust for Thu-Wed)
"WeekNum_Month", 
VAR FirstThursday = 
CALCULATE(MIN([Date]), 
DATESBETWEEN('DateCalendar'[Date], 
EOMONTH([Date], -1) + 1, [Date]), 
WEEKDAY([Date], 2) = 5) 
RETURN DATEDIFF(FirstThursday, [Date], DAY) / 7 + 1, // Week num for each month
"WeekNum_Year", WEEKNUM([Date], 21), // Custom WeekNum based on Thursday to Wednesday
"MaxMonthDays", 
VAR DaysInMonth = CALCULATE(COUNTROWS('YourDataTable'), ALLEXCEPT('DateCalendar', 'DateCalendar'[Month]))
RETURN DaysInMonth // Based on maximum days present in the month
)

If this helped, a Kudos 👍 or Solution mark would be great!🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[_mittalhimanshu]

Also, powerBI is not recognizing DATESBETWEEN('DateCalendar'[Date])

[_mittalhimanshu]

My apologies, I believe I couldn't explain my requirements correctly.

Let me rephrase it. 

So my FY starts from July to June

My weeks starts from Thursday to Wednesday

Let say in a given week (Thur-Wed) 2 months share dates.

Ex March 28 to April 3 2024

• in this week there are 4 days from March and 3 days from April so this whole week should be counted as March Week 5
• Similarly May 30 to June 5 2024

in this week maximum days are from June So the whole week should be considered as June Week 1.

[rajendraongole1 · Accepted solution]

Hi @_mittalhimanshu - I hope you already have a date table created in your model with below new columns 

Please create a calendar table as like below:

DateCalendar =
ADDCOLUMNS(
CALENDAR(DATE(2023, 7, 1), DATE(2024, 6, 30)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Weekday", WEEKDAY([Date], 2), // Monday=1, ..., Sunday=7
"WeekStart",
IF(WEEKDAY([Date], 1) < 5, [Date] - WEEKDAY([Date], 1) + 5, [Date] - WEEKDAY([Date], 1) + 5),
"FiscalMonth",
IF(MONTH([Date]) >= 7, MONTH([Date]) - 6, MONTH([Date]) + 6) // Fiscal month adjustment
)

You can create a calculated column to identify the week number and to determine which month contributes more to that week:

WeekNum =
VAR CurrentWeekStart = [WeekStart]
VAR NextWeekStart = CurrentWeekStart + 7

// Count days in the current week
VAR DaysInCurrentMonth =
ADDCOLUMNS(
FILTER(
CALENDAR(CurrentWeekStart, NextWeekStart - 1),
MONTH([Date]) = MONTH(CurrentWeekStart)
),
"DaysCount", 1
)

VAR DaysInNextMonth =
ADDCOLUMNS(
FILTER(
CALENDAR(CurrentWeekStart, NextWeekStart - 1),
MONTH([Date]) = MONTH(NextWeekStart)
),
"DaysCount", 1
)

VAR MonthDaysCount =
SUMX(DaysInCurrentMonth, [DaysCount]) + SUMX(DaysInNextMonth, [DaysCount])

RETURN
IF(
COUNTROWS(DaysInCurrentMonth) >= COUNTROWS(DaysInNextMonth),
RANKX(
FILTER(
DateCalendar,
[Year] = YEAR(CurrentWeekStart) &&
[Month] = MONTH(CurrentWeekStart)
),
[Date],
,
ASC,
DENSE
),
RANKX(
FILTER(
DateCalendar,
[Year] = YEAR(NextWeekStart) &&
[Month] = MONTH(NextWeekStart)
),
[Date],
,
ASC,
DENSE
)
)

This ensures that your week numbers roll over correctly with the fiscal year:

FiscalWeekNum =
VAR CurrentFiscalYear =
IF(MONTH([Date]) >= 7, YEAR([Date]), YEAR([Date]) - 1)

RETURN
RANKX(
FILTER(
DateCalendar,
[Year] = CurrentFiscalYear &&
[Month] = [FiscalMonth]
),
[Date],
,
ASC,
DENSE
)

Date calendar table FYR:

DateCalendar =
ADDCOLUMNS(
CALENDAR(DATE(2023, 7, 1), DATE(2024, 6, 30)),
"Year", YEAR([Date]),
"Month", MONTH([Date]),
"Day", DAY([Date]),
"Weekday", WEEKDAY([Date], 2),
"WeekStart", IF(WEEKDAY([Date], 1) < 5, [Date] - WEEKDAY([Date], 1) + 5, [Date] - WEEKDAY([Date], 1) + 5),
"FiscalMonth", IF(MONTH([Date]) >= 7, MONTH([Date]) - 6, MONTH([Date]) + 6),
"WeekNum",
VAR CurrentWeekStart = [WeekStart]
VAR NextWeekStart = CurrentWeekStart + 7

VAR DaysInCurrentMonth =
CALCULATE(
COUNTROWS(DateCalendar),
FILTER(DateCalendar, [Date] >= CurrentWeekStart && [Date] < NextWeekStart && MONTH([Date]) = MONTH(CurrentWeekStart))
)

VAR DaysInNextMonth =
CALCULATE(
COUNTROWS(DateCalendar),
FILTER(DateCalendar, [Date] >= CurrentWeekStart && [Date] < NextWeekStart && MONTH([Date]) = MONTH(NextWeekStart))
)

RETURN IF(DaysInCurrentMonth >= DaysInNextMonth,
RANKX(ALL(DateCalendar), [Date], , ASC) + 1,
RANKX(ALL(DateCalendar), [Date], , ASC) + 1)
)

check this and this DAX implementation allows you to create a calendar that meets your specific requirements, including starting the week on Thursday, adjusting week numbers based on the month with the most days, and tracking fiscal years starting in July.