cancel
Showing results for
Did you mean:

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

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
Super User

hi @Ephy ,

try like:

``````dates2 =
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"
)
)``````

6 REPLIES 6
Super User

hi @Ephy ,

you may also try like:

``````dates =
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:

Regular Visitor

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?

Super User

hi @Ephy ,

try like:

``````dates2 =
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"
)
)``````

Regular Visitor

@FreemanZ thank you so much this worked perfectly

Super User

Hi,

Please check the below picture and the attached pbix file.

``````New Calendar =
VAR _calendar =
CALENDAR ( DATE ( 2023, 1, 1 ), DATE ( 2023, 12, 31 ) ),
"@monthnumber", MONTH ( [Date] ),
"@monthname", FORMAT ( [Date], "mmmm" )
)
VAR _datenumber =
_calendar,
"@datenumber", SUMX ( FILTER ( _calendar, [Date] <= EARLIER ( [Date] ) ), 1 )
)
VAR _newmonthnumber =
_datenumber,
"@newmonthnumber", ROUNDUP ( DIVIDE ( [@datenumber], 30 ), 0 )
)
VAR _month =
SUMMARIZE ( _calendar, [@monthnumber], [@monthname] )
VAR _newcalendar =
_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.

Regular Visitor

@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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Fabric Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors