Creating a calendar table each month having 30 days

[Ephy]

Hi, How can I create a calendar table with each month having 30 days. For example January to be from Jan 1 to Jan 30, February to be from Jan 31 to March 1 , March to be fromMarch 2 to March 31 and so on

[FreemanZ]

hi @Ephy ,

you may also try like:

dates = 
ADDCOLUMNS(
    ADDCOLUMNS(
        CALENDAR(DATE(2022,12,1), DATE(2023,3,1)),
        "NewMonthNumber",
        VAR _days = [date] - DATE(YEAR([date]),1,1) +1
        VAR _result = INT(DIVIDE(_days, 30))+1
        RETURN _result
    ),
    "NewMonth",
    SWITCH(
        [NewMonthNumber],
        1, "Jan",
        2, "Feb",
        3, "Mar",
        4, "Apr",
        5, "May",
        6, "Jun",
        7, "Jul",
        8, "Aug",
        9, "Sep",
        10, "Oct",
        11, "Nov",
        12, "Dec",
        "Jan"
    )
)

it worked like:

[Ephy]

Hi @FreemanZ 

thank you so much. But I have one more question my data starts from June 2 and when I put the min and max of my data it didn't give me the intervals. Is there something I need to tweak?  Also, I apologize in advance I just found out that to include the rest of the days(5) the setup of the calendar should be

Jan ,from Jan 1- Jan 30

Feb, Jan 31-Mar 1

March, Mar 2-Mar 31

April, Apr 1- May 1

May, May 2- Jun 1

June, June 2-Jul 1

July, July 2-July 31

August, Aug 1-Aug 30

September, Aug 31- Sep 30

October, Oct 1- Oct 31

November, Nov 1 - Dec 1

December, Dec 2 - Dec 31

Is it possible to set it this way?

[FreemanZ · Accepted solution]

hi @Ephy ,

try like:

dates2 = 
ADDCOLUMNS(
    ADDCOLUMNS(
        CALENDAR(DATE(2022,12,1), DATE(2023,3,1)),
        "NewMonthNumber",
        VAR _m= MONTH([date])
        VAR _d = DAY([date])
        RETURN
        SWITCH(
            TRUE(),
            _m=1 && _d<=30, 1,
            _m=2 || (_m=1&&_d=31 ) || (_m=3 && _d=1 ), 2,
            _m=3&& _d>=2 &&  _d<=31, 3,
            _m=4 || (_m=5&&_d=1 ), 4,
            (_m=5&&_d>=2) || (_m=6&&_d=1 ), 5,
            (_m=6&&_d>=2) || (_m=7&&_d=1 ), 6,
            (_m=7&&_d>=2 ), 7,
            (_m=8&&_d<=30 ), 8,
            (_m=8&&_d=31) || _m=9, 9,
            _m=10, 10,
            _m=11, 11,        
            12
        )
    ),
    "NewMonth",
    SWITCH(
        [NewMonthNumber],
        1, "Jan",
        2, "Feb",
        3, "Mar",
        4, "Apr",
        5, "May",
        6, "Jun",
        7, "Jul",
        8, "Aug",
        9, "Sep",
        10, "Oct",
        11, "Nov",
        "Dec"
    )
)

[Ephy]

@FreemanZ thank you so much this worked perfectly 

[Jihwan_Kim]

Hi,

Please check the below picture and the attached pbix file.

New Calendar = 
VAR _calendar =
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
        "@monthnumber", MONTH ( [Date] ),
        "@monthname", FORMAT ( [Date], "mmmm" )
    )
VAR _datenumber =
    ADDCOLUMNS (
        _calendar,
        "@datenumber", SUMX ( FILTER ( _calendar, [Date] <= EARLIER ( [Date] ) ), 1 )
    )
VAR _newmonthnumber =
    ADDCOLUMNS (
        _datenumber,
        "@newmonthnumber", ROUNDUP ( DIVIDE ( [@datenumber], 30 ), 0 )
    )
VAR _month =
    SUMMARIZE ( _calendar, [@monthnumber], [@monthname] )
VAR _newcalendar =
    ADDCOLUMNS (
        _newmonthnumber,
        "@newmonthname",
            MAXX (
                FILTER ( _month, [@monthnumber] = EARLIER ( [@newmonthnumber] ) ),
                [@monthname]
            )
    )
RETURN
    SUMMARIZE ( _newcalendar, [Date], [@newmonthnumber], [@newmonthname] )

[Ephy]

@Jihwan_Kim  thank you so much. But I have one more question my data starts from June 2 and when I put the min and max of my data it didn't give me the intervals. Is there something I need to tweak?  Also, I apologize in advance I just found out that to include the rest of the days(5) the setup of the calendar should be

Jan ,from Jan 1- Jan 30

Feb, Jan 31-Mar 1

March, Mar 2-Mar 31

April, Apr 1- May 1

May, May 2- Jun 1

June, June 2-Jul 1

July, July 2-July 31

August, Aug 1-Aug 30

September, Aug 31- Sep 30

October, Oct 1- Oct 31

November, Nov 1 - Dec 1

December, Dec 2 - Dec 31

Please let me know if there is a way to achieve this. Thank you