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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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.


Go to My LinkedIn Page


@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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors