Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Ephy
Regular Visitor

Creating a calendar table each month having 30 days

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

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

 

View solution in original post

6 REPLIES 6
FreemanZ
Super User
Super User

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:

FreemanZ_0-1698306726690.png

 

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?

 

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

 

@FreemanZ thank you so much this worked perfectly 

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1698290501437.png

 

 

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

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

@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

 

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors